Menu
I've inherited a database with a quirky schema. Without boring of the details I have an odd use case.
The table contains a filed completed_at that is a timestamp without timezone. What I'm trying to do is order the results by the timestamp where the timestamp is > X
The kicker is X is a numeric (or decimal) value. Trying to convert the timestamp to int is failing or even to string.
If you simply try completed_at > 0 you get
802
In PostgreSQL 2 temporal data types namely timestamp and timestamptz where one is without timezone and the later is with timezone respectively, are supported to store Time and Date to a column. Both timestamp and timestamptz uses 8 bytes for storing timestamp values.
TIMESTAMPTZ is the same as TIMESTAMP WITH TIME ZONE : both data types store the UTC offset of the specified time. TIMESTAMP is an alias for DATETIME and SMALLDATETIME .
The timestamptz datatype is a time zone-aware date and time data type. PostgreSQL stores the timestamptz in UTC value. When you insert a value into a timestamptz column, PostgreSQL converts the timestamptz value into a UTC value and stores the UTC value in the table.
The only answer I found was in this message: https://www.postgresql.org/message-id/3EE8B4ED.1060200%40pgsqldb.com
Basically, I guess, the answer is:
select cast(extract(epoch from current_timestamp) as integer);
54
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
