Convert ISO DateTime to UnixTimestamp in sql where clause - DBeaver client, postgresSQL-CodePudding

I have a column named 'CreatedAt' in postgres (DBeaver client) that is an int8 datatype and holds a unix timestamp value. Example: 1659347651689

I am writing a query that I'd like to input an ISO datet ime in the where clause and have it automatically convert to find the applicable records.

For example: Normally, I'd write:

select * from table1 where CreatedAt = '2022-08-01 09:54:11.000'

I can't do that because the CreatedAt column value is 1659347651689. Is there a way to have it automatically convert and locate the record with that datetime?

I tried this:

`select * from table1 where CreatedAt = date("CreatedAt",strtotime('2022-08-01 09:53:27.000'))`

but strtotime doesn't exist (guessing because it's a Python command. I tried date, dateadd, but no luck)

CodePudding user response：

Your data appears to be in milliseconds, so:

select to_timestamp(1659347651689/1000);

CodePudding user response：

Yes thank you Jeroen Mostert and a_horse_with_no_name (great userid). After reading the links here, I got it.

If anyone else is looking, the answer is:

select * from table1 pfs where timestamp 'epoch' pfs."CreatedAt" /1000 * interval '1 second' = '2022-08-01 09:53:13.000'