Menu
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.
471
Use NOW() to get a timestamptz. This ensures you will get the last 24 hours no matter what your time zone or daylight savings says.
If you want to select the last 24 hours from a datetime field, substitute 'curate()' with 'now()'. This also includes the time.
Here is the SQL to show latest time using now() function. Here is the SQL to get last 1 hour data in MySQL. In the above query, we select only those rows whose order_date falls within past 1 hour interval. We use INTERVAL clause to easily substract 1 hour interval from present time obtained using now() function.
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.
57
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.
30
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
