java - Java 中从时间戳到字符串的 Postgresql 日期

Question

我正在从我的 postgres 数据库中获取时间戳，格式为 yyyy-MM-dd。但问题是我必须在两个日期之间获取表条目，但日期格式没有 Between 运算符。

Select jiraissue.project as ProjectID, project.pname, coalesce(sum(worklog.timeworked),0) as timeworked
from jiraissue, worklog, project
where jiraissue.project = 11350 
and jiraissue.id = worklog.issueid and jiraissue.project = project.id
and date(worklog.updated) BETWEEN 2015-09-16 AND 2015-09-30
group by timeworked, project, project.id

现在 postgres 想要一个显式转换，可能转换为一个字符串，但如果日期也不起作用，则在前面转换为 to_char。有人可以帮助我吗？

Answer 1

这应该可以解决问题:

Select jiraissue.project as ProjectID, project.pname, coalesce(sum(worklog.timeworked),0) as timeworked
from jiraissue, worklog, project
where jiraissue.project = 11350 
and jiraissue.id = worklog.issueid and jiraissue.project = project.id
and date(worklog.updated) >= '2015-09-16'::date 
and date(worklog.updated) <= '2015-09-30'::date 
group by timeworked, project, project.id

假设您使用的是最新的稳定版本 (9.4)，this section of the documentation涵盖日期/时间处理的主题。您可能还想检查 SQL expressions for type casting ，这是解决方案中的 :: 位。