Skip to content

Instantly share code, notes, and snippets.

@Kouni

Kouni/AWS Athena timestamp select query .md

Forked from seanickle/AWS Athena timestamp select query .md
Created January 4, 2023 14:36
Show Gist options
  • Save Kouni/311eb2fa3cf77172ad7c8649367a7b28 to your computer and use it in GitHub Desktop.
Save Kouni/311eb2fa3cf77172ad7c8649367a7b28 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. @seanickle seanickle revised this gist Sep 11, 2018. 1 changed file with 11 additions and 0 deletions.
    11 changes: 11 additions & 0 deletions more casts.md
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,11 @@

    #### 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
    ```
  2. @seanickle seanickle revised this gist Aug 22, 2018. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion AWS Athena timestamp select query .md
    View file
    Original file line number Diff line number Diff line change
    @@ -2,7 +2,7 @@
    * Here, the Athena table `athena_table` has the columns `timestamp, day, month, year, id`
    *
    ```sql
    select timestamp,day,month,year,id
    select timestamp,month,day,year,id
    from athena_table

    WHERE month = 8 and day = 15
  3. @seanickle seanickle created this gist Aug 22, 2018.
    18 changes: 18 additions & 0 deletions AWS Athena timestamp select query .md
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,18 @@
    #### Doing a SELECT on a specific timestamp range
    * Here, the Athena table `athena_table` has the columns `timestamp, day, month, year, id`
    *
    ```sql
    select timestamp,day,month,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