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->