athena query question
0
Hi I have a table in Athena with an attached screenshot.
How i can list all entries in between two days using timestamp column as reference
2개 답변
- 최신
- 최다 투표
- 가장 많은 댓글
3
You can convert bigint to date timestamp as below:
date_format(from_unixtime(bigint_timestamp_column/1000),’%Y-%m-%d %h:%i%p')
You can test this by running this sample:
select date_format(from_unixtime(1688183062393/1000), '%Y-%m-%d %h:%i:%s')
Here is how you'd query your table with condition:
select * from table_name where date_format(from_unixtime(1688183062393/1000), '%Y-%m-%d %h:%i:%s') > date_parse('2023-07-01', '%Y-%m-%d') and date_format(from_unixtime(1688183062393/1000), '%Y-%m-%d %h:%i:%s') < date_parse('2023-07-10', '%Y-%m-%d')
Hope you find this helpful.
Comment here if you have additional questions, happy to help.
Abhishek
0
Select DATE_FORMAT(timestamp,'%Y-%m-%d') where timestamp >= timestamp '2023-08-30' and timestamp <= timestamp '2023-09-01'
Thanks for your answer. This was one of my first tries and didn’t helped.
관련 콘텐츠
AWS 공식업데이트됨 3년 전- AWS 공식업데이트됨 8달 전
Were you able to use this method in your use case, comment here if you have any additional questions?
Thanks for the answer. I tried this earlier and didn’t helped me . What I did is changed columns type and then looked for what I need in the table.