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