CODEWITHSUNDEEP
phppostgresql
malisit
malisit

Reputation: 1258

PostgreSQL get items from last 12 hours

I have the following query to get what I want in PHP file, it does it's job except the WHERE part. I need to get items that have date_time1 column for last 12 hours.

SELECT p.*, t.* 
FROM posts AS p 
LEFT JOIN posted_tweets AS t 
ON p.a_id = t.p_id
WHERE p.date_time1 >= now() AND t.date_time =
( 
    SELECT MAX(date_time) 
    FROM posted_tweets AS t2 
    WHERE t2.p_id = t.p_id
)
    OR t.date_time IS NULL

How exactly should I edit p.date_time1 >= now() part to reach my aim? Thanks.

Upvotes: 29

Views: 23234

Answers (2)

F. Stephen Q
F. Stephen Q

Reputation: 4306

Replace now() with an appropriate time function from here.

EDIT: The actual issue was with the quotations; remember to use single quotes in your PostgreSQL queries.

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521299

The following should restrict to all times occurring in the last 12 hours:

w.date_time1 >= (NOW() - INTERVAL '12 hours' )

Check here for a good discussion of handling datetime in Postgresql, along with examples.

Upvotes: 61

Related Questions