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?
Add a comment
|
2 Answers
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) [...]
7 Comments
Mike Sherrill 'Cat Recall'
Here to_timestamp() is the better choice. It evaluates to a constant. Extracting epoch from the timestamp column has to be evaluated for every row.
IMSoP
@MikeSherrill'Catcall' Good point. The key being that
to_timestamp is defined as IMMUTABLE, so the query planner should know to run it once and cache the result.Mike Sherrill 'Cat Recall'
Both functions are immutable. The difference is that, in this case, to_timestamp() is called with a constant argument, and extract() is called with a different argument on each row. If the OP had stored the Unix epoch in the table, and needed to compare it to a constant value of type timestamp, then extract() would be the better choice.
IMSoP
@MikeSherrill'Catcall' Right you are! I guess what I was fumbling towards was a hint to those not familiar that it is the
immutable attribute of the function that makes this possible, not some internal magic. A user-defined function (for instance, for converting an MS "ticks" value) would get the same performance benefit if it was defined as immutable, but would be evaluated for every row if it was not.IMSoP
@JackGibson The Unix timestamp for "Sat, 09 Feb 2013 13:45:25 UTC" is 1360417525; it looks like you've got millisecond precision rather than seconds.
to_timestamp accepts a float, so you can add a decimal point and get the value you were after: select to_timestamp(1360417525.156). |
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
