skip to Main Content

I have data in this format below:
first column is ID and second column is updated_at

id updated_at
514 1664296267649490
747 1632984531588160
1341 1622023170903700
1889 1667852918885970
4604 1623874503631630
4769 1623874506587930
4813 1623874507218100
4881 1623874507579260
4922 1623874508093550
4992 1623874510601910

the updated_at column is a date which is stored in json format.
I need to write a sql query with where condition on the column "updated_at" where updated_at is > 11/01/23

2

Answers


  1. I am assuming that, updated_at contains the datetime value. so first you need to convert the number into datetime format.

    Select convert(datetime, (convert (int, [updated_at])), 6) as 'convertedDateTime' from table_name;
    

    then above query should return something like 2024-10-10 12:00:00

    Now you can write below query to get desired result.

    SELECT id from table_name WHERE convertedDateTime > `2023-01-11`;
    

    Please apologize for minor syntax errors if any.

    Login or Signup to reply.
  2. create table tbA
    (
    id int,
    jsonData varchar(max)
    )

    insert tbA
    values
    (1,'{"updated_at":1664296267649490}’)
    ,(2,'{"updated_at":1632984531588160}’)

    select id,DATEADD(SECOND, (cast(JSON_VALUE(jsonData,’$.updated_at’) as bigint) / 1000000), ‘1970-01-01 00:00:00’) as dateConverted
    from tbA

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search