Search code examples
postgresqldateepoch

Postgres select record stored as epoch from yesterday

I need to select all records from yesterday. Date in table is stored as epoch timestamp. I tried something like this, but it's not working.

select to_char((SELECT TIMESTAMP WITH TIME ZONE 'epoch' + history.clock * INTERVAL '1 second') , 'YYYY-MM-DD HH24:MI:SS ') AS Data
from history
WHERE
history.clock >=  EXTRACT( epoch FROM current_timestamp - interval '1 day') and
history.clock <=  EXTRACT( epoch FROM current_timestamp - interval '1 day') ;
Solution

  • Well your problem is that you're saying "everything where clock >= 1510444800 and clock <= 1510444800". You're giving yourself a single second range.

    The following query should do what you need. Increase the upper bound of the range by 86399 (There are 86400 seconds in a day, but we already have the first second of the day from our extract when combined with a ::date cast)

    select
    to_char(to_timestamp(history.clock), 'YYYY-MM-DD HH24:MI:SS ') as Data
from
    history
where
    history.clock between extract(epoch from 'yesterday'::date)::integer and (extract(epoch from 'yesterday'::date)::integer + 86399)

    I've also included a few what I consider to be improvements. No need to mess around with intervals for getting yesterday when Postgres has support for casting 'yesterday' to a date. Comparing a value to an upper and lower bound is what the between keyword was made for, and there's a built in function for converting from a unix timestamp.