Date Functions


MySQL includes a range of functions for manipulating date and time values. You learn the most useful functions in this section.

Refer to the online manual for the full list of date and time functions: http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html.


Returning the Current Date and Time

The MySQL function CURDATE() returns the current date; CURTIME() returns the current time, as shown in the following example:

 mysql> SELECT CURDATE(), CURTIME(); +------------+-----------+ | CURDATE()  | CURTIME() | +------------+-----------+ | 2005-10-28 | 16:24:45  | +------------+-----------+ 1 row in set (0.01 sec) 


The NOW() function returns the current date and time as a single value.

 mysql> SELECT NOW(); +---------------------+ | NOW()               | +---------------------+ | 2005-10-28 16:24:45 | +---------------------+ 1 row in set (0.00 sec) 


The actual date and time value returned by these functions is determined by your system's time zone setting. Running NOW() on two servers based in different parts of the world returns two different values.

Time Zones

The time zone upon which all others are calculated is Coordinated Universal Time, abbreviated to UTC. The time in Hawaii is 10 hours behind UTC, written as UTC-10, whereas in Japan, the local time is UTC+9. Daylight saving time can modify this further at certain times of the year.

To return the current date and time using a UTC clock, use UTC_DATE() and UTC_TIME(). To return a single date and time value, use UTC_TIMESTAMP(). The output in the following example is from a MySQL server based on the West Coast of the United States, which is UTC-8:

 mysql> SELECT CURTIME(), UTC_TIME(); +-----------+------------+ | CURTIME() | UTC_TIME() | +-----------+------------+ | 08:48:43  | 16:48:43   | +-----------+------------+ 1 row in set (0.00 sec) 


You can use the CONVERT_TZ() function to convert a time from one time zone to another. Given a date value and two time zone names, CONVERT_TZ() returns the date converted from the first time zone to the second.

In the following example, the date is converted from Pacific Standard Time to Eastern Standard Time:

 mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00',     ->                   'US/Pacific', 'US/Eastern'); +-------------------------------------------------------------+ | CONVERT_TZ('2004-01-01 12:00:00','US/Pacific','US/Eastern') | +-------------------------------------------------------------+ | 2004-01-01 15:00:00                                         | +-------------------------------------------------------------+ 1 row in set (0.00 sec) 


Time Zones

Not all systems support all time zone names. If the previous example does not work, MySQL might not be configured with time zone support. Contact your system administrator or refer to http://dev.mysql.com/doc/refman/5.0/en/time-zone-support.html.


Formatting a Date

To display a date in a different format, use the DATE_FORMAT() function. The first argument is a date; the second argument is a format string, specified using a series of characters prefixed with a % sign. Other characters in the format string are included in the result verbatim.

The following example formats a date using the full textual day and month names, and an ordinal suffix on the date:

 mysql> SELECT DATE_FORMAT(NOW(), 'The date is %W %D %M %Y'); +-----------------------------------------------+ | DATE_FORMAT(NOW(), 'The date is %W %D %M %Y') | +-----------------------------------------------+ | The date is Friday 28th October 2005          | +-----------------------------------------------+ 1 row in set (0.00 sec) 


Table 8.2 shows the full list of format characters for a date.

Table 8.2. Date Format Characters Used by DATE_FORMAT()

Character

Meaning

%a

Abbreviated weekday name (SunSat)

%b

Abbreviated month name (JanDec)

%c

Month number, no leading zero (112)

%D

Day of the month with ordinal suffix (1st31st)

%d

Day of the month (131)

%j

Day of the year, three digits (001366)

%M

Month name (JanuaryDecember)

%m

Month number, two digits (0112)

%U

Week number using Sunday as first day of week (0153)

%u

Week number using Monday as first day of week (0153)

%W

Weekday name (SundaySaturday)

%Y

Year, four digits

%y

Year, two digits


To format a date, you can still use DATE_FORMAT() with a different set of format codes. The following example shows just one way to format the current time returned by NOW():

 mysql> SELECT DATE_FORMAT(NOW(), 'The time is %h:%i:%s %p'); +-----------------------------------------------+ | DATE_FORMAT(NOW(), 'The time is %h:%i:%s %p') | +-----------------------------------------------+ | The time is 04:19:36 PM                       | +-----------------------------------------------+ 1 row in set (0.00 sec) 


Table 8.3 lists all the format characters that can be used to format a time.

Table 8.3. Time Format Characters Used by DATE_FORMAT()

Character

Meaning

%H

Hour on 24-hour clock, two digits (0023)

%h

Hour on 12-hour clock (0112)

%i

Minutes, two digits (0159)

%l

Hour on 12-hour clock, no leading zero (112)

%p

A.M. or P.M.

%r

Time on 12-hour clock as HH:MM:SS

%s

Seconds, two digits (0059)

%T

Time on 24-hour clock as HH:MM:SS


Percent

To include a literal percent character in a date format string, use %%.


Extracting Part of a Date

You can use the EXTRACT() function to return part of the date. Similar behavior can be achieved using DATE_FORMAT(), but whereas that function returns a formatted string, EXtrACT() returns a numeric result.

The syntax of EXtrACT() uses the same interval specifier as date arithmetic, with the keyword FROM. The following example returns the day number part of the current date:

 mysql> SELECT EXTRACT(DAY FROM NOW()); +-------------------------+ | EXTRACT(DAY FROM NOW()) | +-------------------------+ |                      28 | +-------------------------+ 1 row in set (0.01 sec) 


If you use a compound unit, the values are returned as a single number with the most significant value first. The following example returns the year and month from the current date:

 mysql> SELECT EXTRACT(YEAR_MONTH FROM NOW()); +--------------------------------+ | EXTRACT(YEAR_MONTH FROM NOW()) | +--------------------------------+ |                         200510 | +--------------------------------+ 1 row in set (0.00 sec)  


Date Arithmetic

To compute the difference between two dates, use the DATEDIFF() function. Given two date arguments, DATEDIFF() returns the number of days between the second date and the first.

 mysql> SELECT DATEDIFF('2006-01-20', '2005-11-10'); +--------------------------------------+ | DATEDIFF('2006-01-20', '2005-11-10') | +--------------------------------------+ |                                   71 | +--------------------------------------+ 1 row in set (0.00 sec) 


If the second date is greater than the first, the value returned is negative, as shown in this example:

 mysql> SELECT DATEDIFF('2005-12-31', '2006-01-01'); +--------------------------------------+ | DATEDIFF('2005-12-31', '2006-01-01') | +--------------------------------------+ |                                   -1 | +--------------------------------------+ 1 row in set (0.00 sec) 


Time Components

If you run DATEDIFF() on two date values that include a time component, the time elements are simply ignored in the calculation. The calculation is performed on the dates only.


You can use TIMEDIFF() to calculate the difference between two times. The result returned is a time value, as shown in the following example:

 mysql> SELECT TIMEDIFF('12:00', '10:30'); +----------------------------+ | TIMEDIFF('12:00', '10:30') | +----------------------------+ | 01:30:00                   | +----------------------------+ 1 row in set (0.02 sec) 


The arguments to TIMEDIFF() contain date elements, but a time value is still returnedthere is not a date component in the result. The following example shows that when the times are more than a day apart, the time value returned is larger than 24 hours:

 mysql> SELECT TIMEDIFF('2006-01-31 12:00', '2006-01-30 09:30'); +--------------------------------------------------+ | TIMEDIFF('2006-01-31 12:00', '2006-01-30 09:30') | +--------------------------------------------------+ | 26:30:00                                         | +--------------------------------------------------+ 1 row in set (0.00 sec) 


UNIX Time Stamps

UNIX time stamp format is a common way of representing time values as an integer. The numeric value is the number of seconds since midnight on January 1, 1970. The current time stamp value is a 10-digit number.

The UNIX_TIMESTAMP() function returns the time stamp value of a date. If it is called with no arguments, it returns the current time stamp.

 mysql> SELECT UNIX_TIMESTAMP('2006-01-01 12:45:31'); +---------------------------------------+ | UNIX_TIMESTAMP('2006-01-01 12:45:31') | +---------------------------------------+ |                            1136119531 | +---------------------------------------+ 1 row in set (0.00 sec) 


To convert a time stamp to a MySQL date, use FROM_UNIXTIME().

 mysql> SELECT FROM_UNIXTIME(1136119531); +---------------------------+ | FROM_UNIXTIME(1136119531) | +---------------------------+ | 2006-01-01 12:45:31       | +---------------------------+ 1 row in set (0.00 sec) 





Sams Teach Yourself MySQL in 10 Minutes
Sams Teach Yourself MySQL in 10 Minutes
ISBN: 0672328631
EAN: 2147483647
Year: 2006
Pages: 165
Authors: Chris Newman

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net