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'? – a_horse_with_no_name May 30 '17 at 13:30

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 '17 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 '17 at 12:58
  • 1
    There is no datediff() in Postgres (or "SQL") – a_horse_with_no_name May 30 '17 at 13:29
  • OK, was confused by tags and OP wording. – Henrico Bekker May 30 '17 at 13:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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