PostgreSQL Timestamp – Converting SQL Timestamp to Specific Hour

postgresqltimestamp

I need to set the time of a timestamp in SQL.
My results need to be between yesterday at 7 o'clock and today at 7 o'clock.

Here is the query 

WHERE created_at BETWEEN 
    ?::timestamp - INTERVAL '1 day' AND ?::timestamp 
ORDER BY created_at DESC

The value is:

2017-05-30

I've seen the PostgreSQL datetime functions. But I don't know how to specify a time

Here is the query with static datetimes

SELECT 
    * 
FROM 
    "myTable" 
WHERE 
    created_at BETWEEN '2017-05-30 07:00:00' AND '2017-05-30 07:00:00'
ORDER BY 
    created_at DESC

Now I need to replace the datetime strings with a date string and add the time in SQL

Best Answer

You can use current_date (without a time) and then add the 7 hours:

WHERE created_at BETWEEN (current_date - 1) + time '07:00:00' 
                     AND current_date + time '07:00:00'

(current_date - 1) is "yesterday"

Alternatively you could use an interval: 

WHERE created_at BETWEEN (current_date - 1) + interval '7' hour
                     AND current_date + interval '7' hour

I prefer adding a "time" as that is easier to write if you also want to include minutes, e.g. current_date + time '07:30:00'

Related Question