Menu
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?
361
To calculate the difference between the timestamps in PostgreSQL, simply subtract the start timestamp from the end timestamp. Here, it would be arrival - departure . The difference will be of the type interval , which means you'll see it in days, hours, minutes, and seconds.
PostgreSQL compare date is used to compare date between two different dates, which we have used as an input. We can compare the date by using where and between clauses; we can also compare the date using the date_trunc function in PostgreSQL.
The PostgreSQL function LOCALTIMESTAMP returns the current date and time (of the machine running that instance of PostgreSQL) as a timestamp value. It uses the 'YYYY-MM-DD hh:mm:ss. nnnnnnn' format, where: YYYY is a 4-digit year.
Introduction to PostgreSQL timestamp The timestamp datatype allows you to store both date and time. However, it does not have any time zone data. It means that when you change the timezone of your database server, the timestamp value stored in the database will not change automatically.
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) [...]
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
