Question 114869 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Convert ISO DateTime to UnixTimestamp in sql where clause – DBeaver client, postgresSQL

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)

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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'


  2. 0

    Your data appears to be in milliseconds, so:

    select to_timestamp(1659347651689/1000);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search