日付時刻関数の動作確認

mysql> select SYSDATE(),NOW(),CURRENT_DATE,CURRENT_TIME,CURDATE(),CURTIME(),UTC_DATE(),
-> UTC_TIME(),UTC_TIMESTAMP(),CURRENT_TIMESTAMP(),CURRENT_TIME(),CURRENT_DATE()\G
*************************** 1. row ***************************
SYSDATE(): 2009-04-28 12:43:27
NOW(): 2009-04-28 12:43:27
CURRENT_DATE: 2009-04-28
CURRENT_TIME: 12:43:27
CURDATE(): 2009-04-28
CURTIME(): 12:43:27
UTC_DATE(): 2009-04-28
UTC_TIME(): 03:43:27
UTC_TIMESTAMP(): 2009-04-28 03:43:27
CURRENT_TIMESTAMP(): 2009-04-28 12:43:27
CURRENT_TIME(): 12:43:27
CURRENT_DATE(): 2009-04-28
1 row in set (0.00 sec)

mysql> select DAYOFMONTH(CURRENT_DATE);
+--------------------------+
| DAYOFMONTH(CURRENT_DATE) |
+--------------------------+
| 28 |
+--------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_ADD(CURRENT_DATE,INTERVAL 10 DAY);
+----------------------------------------+
| DATE_ADD(CURRENT_DATE,INTERVAL 10 DAY) |
+----------------------------------------+
| 2009-05-08 |
+----------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT ADDDATE(CURRENT_DATE,INTERVAL 10 DAY);
+---------------------------------------+
| ADDDATE(CURRENT_DATE,INTERVAL 10 DAY) |
+---------------------------------------+
| 2009-05-08 |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT CURDATE();
+------------+
| CURDATE() |
+------------+
| 2009-04-28 |
+------------+
1 row in set (0.00 sec)

mysql> SELECT CURDATE() +0;
+--------------+
| CURDATE() +0 |
+--------------+
| 20090428 |
+--------------+
1 row in set (0.00 sec)

mysql> SELECT CURTIME();
+-----------+
| CURTIME() |
+-----------+
| 12:51:30 |
+-----------+
1 row in set (0.00 sec)

mysql> SELECT CURTIME() +0;
+---------------+
| CURTIME() +0 |
+---------------+
| 125136.000000 |
+---------------+
1 row in set (0.00 sec)

mysql>

mysql> SELECT now();
+---------------------+
| now() |
+---------------------+
| 2009-04-28 12:53:01 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE(now());
+-------------+
| DATE(now()) |
+-------------+
| 2009-04-28 |
+-------------+
1 row in set (0.00 sec)

mysql>

mysql> SELECT DATEDIFF(CURDATE(),'2009-06-10');
+----------------------------------+
| DATEDIFF(CURDATE(),'2009-06-10') |
+----------------------------------+
| -43 |
+----------------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATEDIFF(CURDATE(),'2008-04-28');
+----------------------------------+
| DATEDIFF(CURDATE(),'2008-04-28') |
+----------------------------------+
| 365 |
+----------------------------------+
1 row in set (0.00 sec)

mysql>

mysql> SELECT DATEDIFF(CURDATE(),'2009-06-10');
+----------------------------------+
| DATEDIFF(CURDATE(),'2009-06-10') |
+----------------------------------+
| -43 |
+----------------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATEDIFF(CURDATE(),'2008-04-28');
+----------------------------------+
| DATEDIFF(CURDATE(),'2008-04-28') |
+----------------------------------+
| 365 |
+----------------------------------+
1 row in set (0.00 sec)

mysql>
mysql> SELECT DAYNAME(CURDATE());
+--------------------+
| DAYNAME(CURDATE()) |
+--------------------+
| Tuesday |
+--------------------+
1 row in set (0.00 sec)

mysql> SELECT DAYOFMONTH(CURDATE());
+-----------------------+
| DAYOFMONTH(CURDATE()) |
+-----------------------+
| 28 |
+-----------------------+
1 row in set (0.00 sec)

mysql> SELECT DAYOFWEEK(CURDATE());
+----------------------+
| DAYOFWEEK(CURDATE()) |
+----------------------+
| 3 |
+----------------------+
1 row in set (0.00 sec)

mysql> SELECT SECOND(CURTIME());
+-------------------+
| SECOND(CURTIME()) |
+-------------------+
| 57 |
+-------------------+
1 row in set (0.00 sec)

mysql> SELECT DAYOFYEAR(CURDATE());
+----------------------+
| DAYOFYEAR(CURDATE()) |
+----------------------+
| 118 |
+----------------------+
1 row in set (0.00 sec)

mysql>

mysql> SELECT LAST_DAY(CURDATE());
+---------------------+
| LAST_DAY(CURDATE()) |
+---------------------+
| 2009-04-30 |
+---------------------+
1 row in set (0.00 sec)

=====================================================================================
SYSDATE() は、それが実行された時間を戻します。これは NOW() の動作によって異なり、
ステートメントが実行を開始する時間を示す定数時間を戻します。
( ストアド ルーチンまたはトリガ内で、NOW() はルーチンまたは
トリガ文が実行を開始する時間を戻します。)
=====================================================================================

mysql> SELECT NOW(), SLEEP(2), NOW();
+---------------------+----------+---------------------+
| NOW() | SLEEP(2) | NOW() |
+---------------------+----------+---------------------+
| 2009-04-28 13:06:17 | 0 | 2009-04-28 13:06:17 |
+---------------------+----------+---------------------+
1 row in set (2.00 sec)

mysql> SELECT SYSDATE(), SLEEP(2), SYSDATE();
+---------------------+----------+---------------------+
| SYSDATE() | SLEEP(2) | SYSDATE() |
+---------------------+----------+---------------------+
| 2009-04-28 13:06:23 | 0 | 2009-04-28 13:06:25 |
+---------------------+----------+---------------------+
1 row in set (2.00 sec)

mysql>

11.5. 日付時刻関数

Comments are closed.

Post Navigation