Logo         Community
  Trog
Services
The Company
Community
Columns
Your Account
Contact Us
 
 
Date/Time Functions In MySQL
Get a crash course in MySQL's date and time functions.

| The Right Type |

In the MySQL world, a date and/or time value can appear in either of two formats: the machine-readable kind, like this,


20040119122635


or the human-readable kind, like this:


"2004-01-19 12:26:35"


MySQL accepts both these formats - however, you must remember to enclose the second type of value in quotes whenever you use it, as it's technically a string.

Now, in order to use a temporal value in a MySQL table, the corresponding field must be declared as a date/time type. This might sound simple, but it's not - you see, MySQL supports not one, not two, but rather five different date/time data types, ranging from simple types that only hold a date or time value to more complex hybrids containing hour, minute, day, month and year information.

* The DATE type is good for storing date values which do not contain a time component. A 3-byte type, it can store values in the range 1000-01-01 to 9999-12-31.

* The TIME type is the opposite, allowing you to store time values without a date component. This is useful for storing both times and durations, and also uses 3 bytes. Its allowed range of values is -838:59:59 to 838:59:59.

* The YEAR type is only useful if you're looking to store the year component of a date. It accepts both 4- and 2-digit values in the range 1901 to 2155, and uses 1 byte of memory (for year values outside this range, consider using an INT type instead).

Consider the following example, which demonstrates these three types in action:


mysql> CREATE TABLE dummy (d DATE, t TIME, y YEAR);
Query OK, 0 rows affected (0.16 sec)

mysql> INSERT INTO dummy (d, t, y) VALUES (20030908, "11:12:13", 1978);
Query OK, 1 row affected (0.05 sec)

mysql> INSERT INTO dummy (d, t, y) VALUES ("2003-11-10", 231000, 00);
Query OK, 1 row affected (0.05 sec)

mysql> SELECT * FROM dummy;
+------------+----------+------+
| d          | t        | y    |
+------------+----------+------+
| 2003-09-08 | 11:12:13 | 1978 |
| 2003-11-10 | 23:10:00 | 0000 |
+------------+----------+------+
2 rows in set (0.00 sec)


* If you need to specify the date and time together, consider using the DATETIME type, which occupies 8 bytes of memory and accepts values in the range 1000-01-01 00:00:00 to 9999-12-31 23:59:59. Here's an example:


mysql> CREATE TABLE dummy (dt DATETIME);
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO dummy (dt) VALUES ("2004-06-04 09:15");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO dummy (dt) VALUES (20040718061728);
Query OK, 1 row affected (0.06 sec)

mysql> SELECT * FROM dummy;
+---------------------+
| dt                  |
+---------------------+
| 2004-06-04 09:15:00 |
| 2004-07-18 06:17:28 |
+---------------------+
2 rows in set (0.00 sec)


* Finally, one of MySQL's most interesting date/time types is the TIMESTAMP type, used to store...yup, timestamps. Similar to the DATETIME type, this one too allows you to store date and time information in a single field. The difference lies in the fact that MySQL automatically fills the first TIMESTAMP field in a row with the current date and time when the corresponding record is created or updated. Here's an example:


mysql> CREATE TABLE dummy (v VARCHAR(20), ts TIMESTAMP);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO dummy (v) VALUES ('Shazam!');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM dummy;
+---------+----------------+
| v      | ts            |
+---------+----------------+
| Shazam! | 20040119205317 |
+---------+----------------+
1 row in set (0.00 sec)


Note that you can also set a TIMESTAMP field to the current date and time by inserting a NULL value into it. Take a look:


mysql> CREATE TABLE dummy (ts TIMESTAMP);
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO dummy (ts) VALUES (NULL);
Query OK, 1 row affected (0.11 sec)

mysql> SELECT * FROM dummy;
+----------------+
| ts            |
+----------------+
| 20040119205220 |
+----------------+
1 row in set (0.00 sec)


Notice also the difference in how MySQL displays DATETIME and TIMESTAMP values - the former are displayed in human-readable format, while the latter are displayed as numeric machine-readable values without delimiters or spaces.

Oh yeah - just in case you were wondering, MySQL is completely Y2K-compliant, although you should still read the information at http://www.mysql.com/doc/en/Y2K_issues.html in case your application uses 2-digit year values.


How to do Everything with PHP & MySQL
How to do Everything with PHP & MySQL, the best-selling book by Melonfire, explains how to take full advantage of PHP's built-in support for MySQL and link the results of database queries to Web pages. You'll get full details on PHP programming and MySQL database development, and then you'll learn to use these two cutting-edge technologies together. Easy-to-follow sample applications include a PHP online shopping cart, a MySQL order tracking system, and a PHP/MySQL news publishing system.

Read more, or grab your copy now!


previous page more like this  print this article  next page
 
Search...
 
In trog...
Logging With PHP
Building A Quick-And-Dirty PHP/MySQL Publishing System
Output Buffering With PHP
Date/Time Processing With PHP
Creating Web Calendars With The PEAR Calendar Class
more...
 
In the hitg report...
Crime Scenes
Animal Attraction
Lord Of The Strings
more...
 
In boombox...
Patience - George Michael
Think Tank - Blur
My Private Nation - Train
more...
 
In colophon...
Hostage - Robert Crais
The Dead Heart - Douglas Kennedy
Right As Rain - George Pelecanos
more...
 
In cut!...
American Chai
The Core
Possession
more...
 
Find out how you can use this article on your own Web site!


Copyright © 1998-2018 Melonfire. All rights reserved
Terms and Conditions | Feedback