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
I am assuming that, updated_at contains the datetime value. so first you need to convert the number into datetime format.
then above query should return something like
2024-10-10 12:00:00
Now you can write below query to get desired result.
Please apologize for minor syntax errors if any.
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