CODEWITHSUNDEEP
phphtmldatabasepostgresqldatetime
xa.
xa.

Reputation: 345

Using INTERVAL in PostgreSQL to add hours date/time

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?

Upvotes: 1

Views: 21634

Answers (3)

Amir Koklan
Amir Koklan

Reputation: 885

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

Upvotes: 0

Alex.Ritna
Alex.Ritna

Reputation: 1984

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';

Upvotes: 5

xa.
xa.

Reputation: 345

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!

Upvotes: 0

Related Questions