Question 88823 in PostgreSQL Question posted in
The official documentation can be found here.

PostgreSQL Transition: Comparing Timestamps to days

I am currently transitioning from an older version of SQL Server to a PostgreSQL environment. I am having trouble finding a way to compare a timestamp’s age to a set value. This is in the where clause as the goal is to take a stored timestamp and find the difference between it and the current time and then see if that is greater than 90 days old.

The original SQL Server Query:

DATEDIFF(DAY, stored_timestamp,GETDATE())>90

Current Failed PostgreSQL Attmepts:

(stored_timestamp - current_timestamp) > stored_timestamp + interval '90 Days'

I have also used other forms of grabbing current times and type casting them without the interval add on and generally run into the same error. The error most commonly received is “operator does not exist: interval > timestamp” or something of the sort where it shows I can not use the > to compare an interval to other forms of time/date data.

Tags:

2

Answers


  1. 0

    Your SQL Server operation

    DATEDIFF(DAY, stored_timestamp,GETDATE())>90

    in PostgreSQL would be

    extract( day from (current_timestamp -stored_timestamp)) > 90

    in order to do a comparison between integers

    Login or Signup to reply.
  2. 0

    A straightforward but naive approach would be:

    stored_timestamp < current_timestamp - interval '90 day'

    But that would not exactly behave the same. From the SQL server documentation:

    This function returns the count […] of the specified datepart boundaries crossed between the specified startdate and enddate.

    Typically, in SQL Server:

    select datediff(day, '2023-04-28 23:59:59', '2023-04-29 00:00:00')

    Returns 1, since a day boundary was crossed (actually: reached) during this 1 second span.

    You don’t get the same behavior in Postgres, which we can see if we compute the date difference with extract:

    select extract( 
  day from (
      '2023-04-29 00:00:00'::timestamp 
    - '2023-04-28 23:59:59'::timestamp
  )
)

    Returns 0, because a 1 second interval has 0 days.

    If you want the same behavior in Postgres, then you a simple way to proceed is to cast the timestamp to a date first (… at the cost of efficienty, unless you specifically optimize for it):

    stored_timestamp::date < current_date - interval '90 day'
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search