0

I want to query postgres database table which has the column with timestamp in long milliseconds. But I have the time in date format "yyyy-MM-dd HH:mm:ssZ" like this. How can I convert this date format to long milliseconds to run the query?

user3553913
  • 373
  • 3
  • 17
  • Timestamp values should be stored in `timestamp` columns, not `bigint` –  Oct 05 '20 at 05:55

1 Answers1

0

You can either convert your long value to a proper timestamp:

select *
from the_table
where to_timestamp(the_millisecond_column / 1000) = timestamp '2020-10-05 07:42'

Or extract the seconds from the timestamp value :

select *
from the_table
where the_millisecond_column = extract(epoch from timestamp '2020-10-05 07:42') * 1000

The better solution is however to convert that column to a proper timestamp column to avoid the constant conversion between (milliseconds) and proper timestamp values