Using INTERVAL in PostgreSQL to add hours date/time

Question

Here is the code in PostgreSQL I am trying to run:

select DATE_ADD(whenbooked,INTERVAL 4 HOUR) from booking WHERE id = 12310;

OR I try to run this code:

select DATE_ADD('2010-11-19 01:11:22',INTERVAL 4 HOUR)

Both codes access date/time stored in the same manner:

2010-11-19 01:11:22

PostgreSQL error code is this:

ERROR: syntax error at or near "4"

it is referring to the '4' in the line 'INTERVAL 4 HOUR'.

I can't figure out what the issue is.

I need to compare a time/stamp (written in exactly the same format as above) with the stored time/date PLUS 4 hours. So the desired end result is to return: '2010-11-19 05:11:22'.

If this can be done in PHP or directly in SQL?

Can you show it with your php code ? I guess there must be some quote issues over there. — Rikesh
– Rikesh, Commented May 22, 2014 at 6:32
select DATE_ADD('2010-11-19 01:11:22',INTERVAL 4 HOUR) works for me ... — Maxim Krizhanovsky
– Maxim Krizhanovsky, Commented May 22, 2014 at 6:32
select DATE_ADD('2010-11-19 01:11:22',INTERVAL 4 HOUR) works for me too — Parag Tyagi
– Parag Tyagi, Commented May 22, 2014 at 6:33
I am just typing these codes into PgAdmin. It runs the sql codes directly................I got that from the php website. I suppose there is no reason why it shouldn't work. In that case, what could be the possible reasons it doesn't work for me ? — xa.
– xa., Commented May 22, 2014 at 6:33

Alex.Ritna · Accepted Answer · 2014-05-22 06:52:26Z

5

Based on your comment

I am just typing these codes into PgAdmin.

It looks like you're actually using PostgreSQL not MySQL.

You can view your existing code running on PostgreSQL in action on PostgreSQL here which gives the same error as you get above.

To correctly work with dates in PostgreSQL, you can view a list of date functions on the PostgreSQL documentation site here

What you're trying to do is this:

SELECT TIMESTAMP '2010-11-19 01:11:22' + INTERVAL '4 HOURS';

answered May 22, 2014 at 6:52

Alex.Ritna

1,9842 gold badges17 silver badges26 bronze badges

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

Comments

xa. · Accepted Answer · 2014-05-22 06:46:42Z

0

I found a method that works for me in PHP:

$desiredEndTime = strtotime("2010-11-19 01:11:22 + 10 hours");
echo date("Y-m-d H:i:s", $desiredendtime);

Looks like it converts the date/time into a weird-looking integer. And then on the second line it converts it back into my desired format. Now I can just insert that back into SQL.

Thanks!

answered May 22, 2014 at 6:46

xa.

3452 gold badges5 silver badges14 bronze badges

1 Comment

Alex.Ritna Over a year ago

I have added an answer which explains why it wasn't working in pure SQL for you including a fix.

Amir Koklan · Accepted Answer · 2014-05-22 06:53:10Z

0

Convert it simply to timestamp with "strtotime" method and then you can do whatever you like with that

answered May 22, 2014 at 6:53

Amir Koklan

8852 gold badges12 silver badges34 bronze badges

Collectives™ on Stack Overflow

Using INTERVAL in PostgreSQL to add hours date/time

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related