Previous Next JavaScript must be enabled to correctly display this content

## Working with Timestamps

To specify a timestamp value in a query, provide it as a string, and cast it to a Timestamp data type. For example:

    sql-> SELECT id, firstname, lastname FROM Persons WHERE
    lastLogin = CAST("2016-10-19T09:18:05.5555" AS TIMESTAMP);
     +----+-----------+----------+
     | id | firstname | lastname |
     +----+-----------+----------+
     |  4 | Peter     | Smith    |
     +----+-----------+----------+

    1 row returned

Timestamp queries often involve a range of time, which requires multiple casts:

    sql-> SELECT id, firstname, lastname, lastLogin FROM Persons WHERE
    lastLogin > CAST("2016-11-01" AS TIMESTAMP) AND
    lastLogin < CAST("2016-11-30" AS TIMESTAMP);
     +----+-----------+----------+--------------------------+
     | id | firstname | lastname |        lastLogin         |
     +----+-----------+----------+--------------------------+
     |  3 | John      | Morgan   | 2016-11-29T08:21:35.4971 |
     |  2 | John      | Anderson | 2016-11-28T13:01:11.2088 |
     |  5 | Dana      | Scully   | 2016-11-08T09:16:46.3929 |
     +----+-----------+----------+--------------------------+

    3 rows returned

You can also use various Timestamp functions to return specific time and date values from the Timestamp data. For example:

    sql-> SELECT id, firstname, lastname,
        year(lastLogin) AS Year,
        month(lastLogin) AS Month,
        day(lastLogin) AS Day,
        hour(lastLogin) AS Hour,
        minute(lastLogin) AS Minute
    FROM Persons;
     +----+-----------+----------+------+-------+-----+------+--------+
     | id | firstname | lastname | Year | Month | Day | Hour | Minute |
     +----+-----------+----------+------+-------+-----+------+--------+
     |  3 | John      | Morgan   | 2016 |    11 |  29 |    8 |     21 |
     |  2 | John      | Anderson | 2016 |    11 |  28 |   13 |      1 |
     |  4 | Peter     | Smith    | 2016 |    10 |  19 |    9 |     18 |
     |  5 | Dana      | Scully   | 2016 |    11 |   8 |    9 |     16 |
     |  1 | David     | Morrison | 2016 |    10 |  29 |   18 |     43 |
     +----+-----------+----------+------+-------+-----+------+--------+

Alternatively, use the EXTRACT function:

    sql-> SELECT id, firstname, lastname,
        EXTRACT(YEAR FROM lastLogin) AS Year,
        EXTRACT(MONTH FROM lastLogin) AS Month,
        EXTRACT(DAY FROM lastLogin) AS Day,
        EXTRACT(HOUR FROM lastLogin) AS Hour,
        EXTRACT(MINUTE FROM lastLogin) AS Minute
    FROM Persons;
     +----+-----------+----------+------+-------+-----+------+--------+
     | id | firstname | lastname | Year | Month | Day | Hour | Minute |
     +----+-----------+----------+------+-------+-----+------+--------+
     |  3 | John      | Morgan   | 2016 |    11 |  29 |    8 |     21 |
     |  4 | Peter     | Smith    | 2016 |    10 |  19 |    9 |     18 |
     |  1 | David     | Morrison | 2016 |    10 |  29 |   18 |     43 |
     |  2 | John      | Anderson | 2016 |    11 |  28 |   13 |      1 |
     |  5 | Dana      | Scully   | 2016 |    11 |   8 |    9 |     16 |
     +----+-----------+----------+------+-------+-----+------+--------+

    5 rows returned
    sql->