CODEWITHSUNDEEP
javastringpostgresqldate
wg15music
wg15music

Reputation: 199

Postgresql Date from Timestamp to String in Java

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?

Upvotes: 2

Views: 518

Answers (1)

zb226
zb226

Reputation: 10500

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.

Upvotes: 3

Related Questions