Postgresql extracting 'epoch' from timestamp cuts off last date in date range-CodePudding

My table has the column event_ts with column type numeric.

Here is my query:

select 
    min(to_timestamp(event_ts)), max(to_timestamp(event_ts))
from 
    table1  
where 
    event_ts >= extract('epoch' from '2021-07-01'::timestamp) and 
    event_ts <= extract('epoch' from '2021-07-31'::timestamp)

However, the results are

min: 2021-06-30 20:00:00.000 -0400  
max: 2021-07-30 20:00:00.000 -0400

I would think the where clause would include data from 2021-07-01 to 2021-07-31. There is data for July 31st, 2021.

Why does this query start at 2021-06-30 and end 2021-07-30?

CodePudding user response：

show timezone;
  TimeZone  
------------
 US/Pacific

 select extract('epoch' from '2021-07-01'::timestamp);
      extract      
-------------------
 1625097600.000000

select to_timestamp(1625097600);;
      to_timestamp       
-------------------------
 06/30/2021 17:00:00 PDT

select extract('epoch' from '2021-07-01'::timestamptz);
      extract      
-------------------
 1625122800.000000
(1 row)

test(5432)=# select to_timestamp(1625122800);
      to_timestamp       
-------------------------
 07/01/2021 00:00:00 PDT

So by using timestamp you are creating a local time offset by the timezone offset. Using timestamptz will return a timestamp at 0:00:00.

This is because from here:

https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT

epoch

For timestamp with time zone values, the number of seconds since 1970-01-01 00:00:00 UTC (negative for timestamps before that); for date and timestamp values, the nominal number of seconds since 1970-01-01 00:00:00, without regard to timezone or daylight-savings rules; for interval values, the total number of seconds in the interval

Epoch is based on UTC timezone.

Not sure why you are using epoch anyway?

Why not?:

...
where
    event_ts  between '2021-07-01'::timestamptz and '2021-07-31'::timestamptz