Date and Time : MySQL Data and Time

MySQL really is the best way to store data. Don't be afraid, its dead simple once you get going, and its the best tool you'll ever come across when programming websites.

MySQL Field Types

MySQL has the ability to store dates and times. It has fields such as DATE which stores 'YYYY-MM-DD dates'.
DATETIME stores date and times in the format 'YYYY-MM-DD HH:MM:SS'.

TIMESTAMP[(M)] logs the last UPDATE/INSERT date/time, based upon its length (M) definition.

TIME stores the time in the 'HH:MM:SS' format, but like the other fields accepts numerous data types.
YEAR[(2|4)] stores the year, in either (2) YY, or (4) YYYY formats.

Inserting Date and Time into MySQL

The preffered data type for dates and time is the format it will be stored in. MySQL does alloy some flexibility however. Inserting '1979-6-9' is the same as '1979-06-09' for a DATE field. You can even get away with 19760609. MySQL automatically reformats this to '1979-06-09'.

If you want to store the current time (and you didn't use a TIMESTAMP field), then use the NOW() function. Very simple:
UPDATE myTable SET my_date=NOW() WHERE id=5

Selecting Date and Time from MySQL

The standard output of fields such as DATE are sometimes okay just to echo on a page. However, if you want to use your own formatting, the method I find best is to output the date/time as a UNIX timestamp. (Now where have you heard that before..)
SELECT UNIX_TIMESTAMP(my_date) as my_date FROM myTable WHERE id=7

This produces the stored time measured in the number of seconds since the Unix Epoch (January 1 1970 00:00:00 GMT).

Why? This is great beacause it works with the date and strftime functions, and is the same sort of thing produced by the mktime() function.

Ooh, isn't this getting exciting. We now can get times out of a mySQL database ready for plugging straight into our PHP documents.

Read on.

Author: Markavian
Last edited on: 8th Dec, 12:50 am
Please rate this article.

« Prev page 'Time and Mktime' « » Next Page 'Adding Time' »

tutorial Pages

Introduction
Date and Strftime
Time and Mktime
MySQL Data and Time
Adding Time