seanickle/AWS Athena timestamp select query .md

Last active September 4, 2023 00:51

Star (5) You must be signed in to star a gist
Fork (1) You must be signed in to fork a gist

Learn more about clone URLs
Clone this repository at <script src="https://gist.github.com/seanickle/10e091da41bb6c4218abf1182397ca2d.js"></script>
Save seanickle/10e091da41bb6c4218abf1182397ca2d to your computer and use it in GitHub Desktop.

Download ZIP

AWS Athena timestamp select query

Raw

AWS Athena timestamp select query .md

Doing a SELECT on a specific timestamp range

Here, the Athena table athena_table has the columns timestamp, day, month, year, id

select timestamp,month,day,year,id
from athena_table

WHERE month = 8 and day = 15
AND (CAST(timestamp as varchar) BETWEEN '2018-08-15 22:00:00' AND '2018-08-15 22:10:00')
                                                                     
ORDER BY timestamp DESC

x	timestamp	month	day	year	id
1	2018-08-15 22:03:25.296	15	8	2018	12344
2	2018-08-15 22:02:59.266	15	8	2018	12345

Raw

more casts.md

length can also be checked on a varchar field

SELECT * FROM blah WHERE length(blah_field) = 10

can treat a string field as a number as well..

SELECT * FROM blah  WHERE (cast(blah_numeric_string_field as double)) >= 10