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:


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

Here is the query with static datetimes

    created_at BETWEEN '2017-05-30 07:00:00' AND '2017-05-30 07:00:00'
    created_at DESC

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

  • created_at BETWEEN '2017-05-30 07:00:00' AND '2017-05-30 07:00:00' is effectively the same as created_at = '2017-05-30 07:00:00' did you mean BETWEEN '2017-05-29 07:00:00' AND '2017-05-30 07:00:00'?
    – user1822
    May 30, 2017 at 13:30

2 Answers 2


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'

  • Beware that this will give the wrong answer on the days when Daylight Saving Time begins and ends.
    – jjanes
    May 30, 2017 at 19:06

Since you didn't give any sample data, I based it on create date on databases.

select name, datediff(day, create_date, getdate()) DaysDiff from sys.databases
**where DATEDIFF(DAY, create_date,getdate()) > 1**

You'll need to evaluate days difference, something like:

where DATEDIFF(DAY, create_at, getdate()) > 1 
  • I added a sample how it looks with static strings
    – Phil795
    May 30, 2017 at 12:58
  • 1
    There is no datediff() in Postgres (or "SQL")
    – user1822
    May 30, 2017 at 13:29
  • OK, was confused by tags and OP wording. May 30, 2017 at 13:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.