タイムスタンプの操作

問合せでタイムスタンプ値を指定するには、その値を文字列として指定し、タイムスタンプ・データ型にキャストします。たとえば:

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 

タイムスタンプの問合せでは、通常、時間の範囲が使用されるため、複数のキャストが必要になります。

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 

様々なタイムスタンプ関数を使用して、タイムスタンプ・データから特定の時間と日付の値を戻すこともできます。たとえば:

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

または、次のようにEXTRACT関数を使用します。

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