CODEWITHSUNDEEP
sqlpostgresql
Jack Gibson
Jack Gibson

Reputation: 1841

Postgres compare timestamp with long unix timestamp

I have a timestamp attribute in a table on which I want to place a condition in a sql query where the condition value is a unix timestamp (i.e. numeric long value). 

[...] table.timestampattr > 6456454654 [...]

How can I do this?

Upvotes: 8

Views: 11457

Answers (2)

Kaveh Naseri
Kaveh Naseri

Reputation: 1266

You can create a constant and set it to the current data and then select it like below:

    WITH myconstants (current_unix_date) as (
       values (extract(epoch from now() at time zone 'utc')) 
    )

    SELECT * From table, myconstants
    WHERE table.target_date_time_unix > myconstants.current_unix_date

Upvotes: 0

IMSoP
IMSoP

Reputation: 97658

You can use extract(epoch from ...) to get a Unix timestamp from any of the PostgreSQL time and date types (see Date/Time functions in manual).

So your query fragment could be written:

[...] extract(epoch from table.timestampattr) > 6456454654 [...]

Alternatively, the to_timestamp function performs the opposite conversion, so you could also write:

[...] table.timestampattr > to_timestamp(6456454654) [...]

Upvotes: 14

Related Questions