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 Risposte
- Più recenti
- Maggior numero di voti
- Maggior numero di commenti
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.
Contenuto pertinente
AWS UFFICIALEAggiornata 2 anni fa- AWS UFFICIALEAggiornata 4 anni fa
- AWS UFFICIALEAggiornata 8 mesi fa
- AWS UFFICIALEAggiornata 3 anni fa
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.