Arguably, of all the MySQL data types, the trickiest to handle properly are date and time types. You need a lot of practice to work with them comfortably. Fields with these types store date and time values in the following formats:
| Data Type | Data Format |
|---|---|
| DATE | 2004-01-01 |
| TIME | 12:00:00 |
| DATETIME | 2004-01-01 12:00:00 |
| TIMESTAMP | 20000101120000 |
| YEAR | 2004 |
If you attempt to insert illegally formatted date and/or time values into fields with these data types, the relevant entry is filled with 0s. For example, inserting the string Pads into a TIME type field results in the value "00000000000000" being saved in the field:
mysql> select accessdate from access_log where userid = 'Pads' AND page ='';
+----------------+
| accessdate |
+----------------+
| 00000000000000 |
+----------------+
1 row in set (0.00 sec)
The MySQL server provides a set of built-in date- and time-related functions that can help you to ensure that you insert correct values. You've already seen the now() function, which returns the current date and time in DATETIME format:
mysql> select now();
+-------------------------+
| now() |
+-------------------------+
| 2004-03-04|19:30:15 |
+----------+--------------+
1 row in set (0.00 sec)
And the curdate() and curtime() functions, which return the same as separate DATE and TIME type values:
mysql> select curdate(), curtime();
+------------+-----------+
| curdate() | curtime() |
+------------+-----------+
| 2004-03-04 | 19:31:33 |
+------------+-----------+
1 row in set (0.00 sec)
To work with a TIMESTAMP field, you can specify either the date and time to insert in a string of 14 digits, or NULL to store the current system date and time in the same format. To properly update the access log (completing the example begun earlier), you'd say:
mysql> UPDATE access_log
-> SET visitcount = visitcount + 1, accessdate = NULL
-> WHERE userid = 'Dodge' AND page = '/score.html';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
The date_format() server function returns a formatted date and time value. It takes a DATE or DATETIME type argument, followed by a formatting string argument. Assuming that lastaccesstime is of the DATE or DATETIME type, you could say:
mysql> SELECT date_format('2004-03-04 22:23:00', '%M %e, %Y');
+-------------------------------------------------+
| date_format('2004-03-04 22:23:00', '%M %e, %Y') |
+-------------------------------------------------+
| March 4, 2004 |
+-------------------------------------------------+
1 row in set (0.03 sec)
There are more than 30 specifiers you can use to construct the formatting string. Here are some of the more common ones:
| Specifier | Description |
|---|---|
| %s | Second in 2-digit numeric form (00,01...) |
| %i | Minute in 2-digit numeric form (00,01...) |
| %H | Hour in 2-digit 24-hour numeric form (00,01, ...) |
| %h | Hour in 2-digit 12-hour numeric form (00,01... ) |
| %T | Time in 24-hour form (hh:mm:ss) |
| %r | Time in 12-hour form (hh:mm:ss AM|PM) |
| %W | Weekday name (Monday, Tuesday, Wednesday...) |
| %a | Abridged weekday name (Mon, Tue, Wed...) |
| %d | Day of the month in 2-digit numeric form (01,02,03...) |
| %e | Day of the month in numeric form (1,2,3...) |
| %D | Day of the mouth with an ordinal suffix (1st, 2nd, 3rd...) |
| %M | Name of the month (January, February...) |
| %b | Abridged name of the month (Jan, Feb...) |
| %Y | Year in 4-digit numeric form |
| %y | Year in 2-digit numeric form |
For a complete list, please see the MySQL documentation at www.mysql.com/doc/en/Date_and_time_functions.html#IDX1335.
In the view_record() function of the user record viewer script in the last chapter, you formatted the accessdate field value with the following lines of code:
$accessdate = substr($query_data["accessdate"], 0, 4) . '-' .
substr($query_data["accessdate"], 4, 2) . '-' .
substr($query_data["accessdate"], 6, 2) . ' ' .
substr($query_data["accessdate"], 8, 2) . ':' .
substr($query_data["accessdate"], 10, 2) . ':' .
substr($query_data["accessdate"], 12, 2);
which produced the date and time in this format:
2004-01-22 10:31:35
Now you can achieve the same goal with a single query:
mysql> SELECT date_format(accessdate, '%Y-%m-%d % H:%i:%s')
-> FROM access_log WHERE userid='Dodge';
+------------------------------------------------+
| date_format(accessdate, '%Y-%m-%d %H:%i:%s') |
+------------------------------------------------+
| 2004-03-11 12:22:49 |
| 2004-01-25 16:41:33 |
+------------------------------------------------+
2 rows in set (0.00 sec)
If you want to know the weekday name of the last access date for a given user, make the following query using the dayname() server function that returns the weekday name of a given date value:
mysql> SELECT accessdate, dayname(accessdate) FROM access_log
-> WHERE userid='Dodge';
+----------------+---------------------+
| accessdate | dayname(accessdate) |
+----------------+---------------------+
| 20040311122249 | Thursday |
| 20040125164133 | Sunday |
+----------------+---------------------+
2 rows in set (0.01 sec)
The to_days() server function calculates the total number of days from the year 0 A.D.:
mysql> SELECT to_days(accessdate) FROM access_log
-> WHERE userid='Dodge';
+---------------------+
| to_days(accessdate) |
+---------------------+
| 732016 |
| 731970 |
+---------------------+
2 rows in set (0.00 sec)
Suppose you want to know how many days have passed since a user's last visit to your site:
mysql> SELECT userid, to_days(now()) - to_days(accessdate)
-> FROM access_log GROUP BY userid LIMIT 5;
+--------+--------------------------------------+
| userid | to_days(now()) - to_days(accessdate) |
+--------+--------------------------------------+
| Brian | 7 |
| Dodge | 0 |
| Greeny | 47 |
| Mac | 49 |
| Nicrot | 47 |
+--------+--------------------------------------+
5 rows in set (0.01 sec)
You can see that the user Mac has not been to the site for 49 days. If you were upset about this, you could delete users who haven't visited your site for more than 48 days by issuing the following query:
mysql> DELETE FROM access_log WHERE to_days(now()) - to_days(accessdate) > 48;
Query OK, 1 row affected (0.01 sec)
This is just a sample of the many date- and time-related server functions MySQL provides.
Please refer to the MySQL online reference manual for more information.
