Thursday, August 2, 2007

Postgresql Timestamp + interval

Jumping into SQL with EntityWhereString, I was trying to do some date arithmetic. I had a timestamp and an integer number of days. I wanted to add the number of days to the timestamp and work with the resulting timestamp.

the Postgresql syntax for this is
SELECT timestamp '2001-09-28 01:00' + interval '23 hours' from myReports

so I got this far:

select tx_stamp + interval '23 days' from myReports

and that worked but when I tried using my integer column "days_to_complete" for the interval, the syntax wasn't clear.

It turns out I needed to concatenate cast my days_to_complete into a string:

days_to_complete||' days'
and then cast the resulting string into an INTERVAL:

select tx_stamp + (days_to_complete||' days')::INTERVAL from myReports
and that worked.

7 comments:

Anonymous said...

thnks.

i was looking just for this.

Anonymous said...

same! thanks :)

kumkee said...

Exactly what I needed thanks!!

Anonymous said...

thanks, that really helped me out. cheers

Anonymous said...

Hey good Job! This was tricky and you did it well! truly useful

John said...

Thanks for this post. It saved my day and (your solution) was clear and concise. Explaining the solution helped me understand the concept so I can apply the same technique in multiple circumstances.

Anonymous said...

I'd been struggling with trying to come up with the right syntax for that for hours. Thank you!