Site Admin
Joined: 10 Dec 2007
Posts: 143
Location: New Zealand
|
In Database world, often you will need to deal with dates. In this tutorial, I will cover some useful functions supported by MySQL for date/time.
 1. Current Date/Time
Its often you'll need to get system's current date & time. By using NOW() function will return server's date & time:
| Code: | mysql> SELECT NOW();
+---------------------+
| NOW() |
+---------------------+
| 2010-06-05 00:12:58 |
+---------------------+
1 row in set (0.00 sec) |
MySQL uses date/time format of YYYY-MM-DD hh:mm:ss
2. Current Date/Time in UTC
Very similar to what I've shown before, but you'll need to use UTC_TIMESTAMP() function instead of NOW().
| Code: | mysql> SELECT UTC_TIMESTAMP();
+---------------------+
| UTC_TIMESTAMP() |
+---------------------+
| 2010-06-04 12:14:03 |
+---------------------+
1 row in set (0.00 sec) |
3. Add time(interval)
There's a function called DATE_ADD() to add values on top of your existing timestamp:
| Code: | mysql> SELECT DATE_ADD(NOW(),INTERVAL 1 DAY);
+--------------------------------+
| DATE_ADD(NOW(),INTERVAL 1 DAY) |
+--------------------------------+
| 2010-06-06 00:18:03 |
+--------------------------------+
1 row in set (0.00 sec)
mysql> SELECT DATE_ADD('2010-05-05 00:00:00',INTERVAL 1 WEEK);
+-------------------------------------------------+
| DATE_ADD('2010-05-05 00:00:00',INTERVAL 1 WEEK) |
+-------------------------------------------------+
| 2010-05-12 00:00:00 |
+-------------------------------------------------+
1 row in set (0.00 sec) |
For INTERVAL, you can have either: SECOND, MINUTE, HOUR, DAY, WEEK, MONTH and YEAR.
4. Substitute time(interval)
Date substitution is very similar to DATE_ADD, you'll need to use DATE_SUB function:
| Code: | mysql> SELECT DATE_SUB(NOW(),INTERVAL 2 WEEK);
+---------------------------------+
| DATE_SUB(NOW(),INTERVAL 2 WEEK) |
+---------------------------------+
| 2010-05-22 00:23:07 |
+---------------------------------+
1 row in set (0.00 sec)
mysql> SELECT DATE_SUB('2010-05-05 00:00:00',INTERVAL 1 WEEK);
+-------------------------------------------------+
| DATE_SUB('2010-05-05 00:00:00',INTERVAL 1 WEEK) |
+-------------------------------------------------+
| 2010-04-28 00:00:00 |
+-------------------------------------------------+
1 row in set (0.00 sec) |
Values for INTERVALs are as same as DATE_ADD function.
_________________
Paul KH Kim
http://www.onlinesolution.co.nz |
|
