Postgresql Date from Timestamp to String in Java

Question

I am getting a timestamp as date from my postgres database with the format of yyyy-MM-dd. But the problem is that I have to get table entries BETWEEN two dates, but the date format does not have the Between operator.

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

Now postgres wants an explicit cast, probably to a String, but to_char in front if the date wo't work either. Could somebody help me?

zb226 · Accepted Answer · 2015-09-30 12:51:10Z

3

This should do the trick:

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

Assuming you're on the most recent stable version (9.4), this section of the documentation covers the topic of date/time handling. You might also want to check the SQL expressions for type casting, which is the :: bit in the solution.

edited Sep 30, 2015 at 12:51

answered Sep 30, 2015 at 12:27

zb226

10.7k6 gold badges57 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

wg15music Over a year ago

thank, will accept the answer when I am able to do it in 5 minutes :)

Collectives™ on Stack Overflow

Postgresql Date from Timestamp to String in Java

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related