Date and Time Functions - SQL Statements and Functions - MySQL in a Nutshell (2008)

MySQL in a Nutshell (2008)

Part II. SQL Statements and Functions

Chapter 12. Date and Time Functions

By using temporal data type columns, you can use several built-in functions offered by MySQL. This chapter presents those functions. Currently, five temporal data types are available: DATE, TIME, DATETIME, TIMESTAMP, and YEAR. You would set a column to one of these data types when creating or altering a table. See the descriptions of CREATE TABLE and ALTER TABLE in Chapter 6 for more details. The DATE column type can be used for recording just the date. It uses the yyyy-mm-dd format. The TIME column type is for recording time in the hhh:mm:ss format. To record a combination of date and time, use DATETIME: yyyy-mm-dd hh:mm:ss. The TIMESTAMP column is similar to DATETIME, but it is more limited in its range of allowable time: it starts at the Unix epoch time (i.e., 1970-01-01) and stops at the end of 2037. Plus, it has the distinction of resetting its value automatically when the row in which it is contained is updated, unless you specifically instruct MySQL otherwise. Finally, the YEAR data type is used only for recording the year in a column. For more information on date and time data types, see Appendix B.

Any function that calls for a date or a time data type will also accept a combined datetime data type. MySQL requires that months range from 0 to 12 and that days range from 0 to 31. Therefore, a date such as February 30 would be accepted prior to version 5.0.2 of MySQL. Beginning in version 5.0.2, MySQL offers more refined validation that would reject such a date. However, some date functions accept 0 for some or all components of a date, or incomplete date information (e.g., 2008-06-00). As a general rule, the date and time functions that extract part of a date value usually accept incomplete dates, but date and time functions that require complete date information return NULL when given an incomplete date. The descriptions of these functions in this chapter indicate which require valid dates and which don’t, as well as which return 0 or NULL for invalid dates.

The bulk of this chapter consists of an alphabetical listing of date and time functions, with explanations of each. Each of the explanations include an example of the function’s use, along with a resulting display, if any. For the examples in this chapter, I used the scenario of a professional services firm (e.g., a law firm or an investment advisory firm) that tracks appointments and seminars in MySQL. For help locating the appropriate function, see the next section or the index at the end of this book.

Date and Time Functions Grouped by Type

Following are lists of date and time functions, grouped according to their purpose: to retrieve the date or time, to extract an element from a given date or time, or to perform calculations on given dates or times.

Determining the Date or Time

CURDATE(), CURRENT_DATE(), CURRENT_TIME(), CURRENT_TIMESTAMP(), CURTIME(), LOCALTIME(), LOCALTIMESTAMP(), NOW(), SYSDATE(), UNIX_TIMESTAMP(), UTC_DATE(), UTC_TIME(), UTC_TIMESTAMP().

Extracting and Formatting the Date or Time

DATE(), DATE_FORMAT(), DAY(), DAYNAME(), DAYOFMONTH(), DAYOFWEEK(), DAYOFYEAR(), EXTRACT(), GET_FORMAT(), HOUR(), LAST_DAY(), MAKEDATE(), MAKETIME(), MICROSECOND(), MINUTE(), MONTH(), MONTHNAME(), QUARTER(),SECOND(), STR_TO_DATE(), TIME(), TIME_FORMAT(), TIMESTAMP(), WEEK(), WEEKDAY(), WEEKOFYEAR(), YEAR(), YEARWEEK().

Calculating and Modifying the Date or Time

ADDDATE(), ADDTIME(), CONVERT_TZ(), DATE_ADD(), DATE_SUB(), DATEDIFF(), FROM_DAYS(), FROM_UNIXTIME(), PERIOD_ADD(), PERIOD_DIFF(), SEC_TO_TIME(), SLEEP(), SUBDATE(), SUBTIME(), TIME_TO_SEC(), TIMEDIFF(), TIMESTAMPADD(),TIMESTAMPDIFF(), TO_DAYS().

Date and Time Functions in Alphabetical Order

The rest of the chapter lists each function in alphabetical order.

Name

ADDDATE()

Synopsis

ADDDATE(date, INTERVAL value type)

ADDDATE(date, days)

This function adds the given interval of time to the date or time provided. This is a synonym for DATE_ADD(); see its definition later in this chapter for details and interval types. The second, simpler syntax is available as of version 4.1 of MySQL. This shorthand syntax does not work, though, with DATE_ADD(). Here is an example:

UPDATE seminars

SET seminar_date = ADDDATE(seminar_date, INTERVAL 1 MONTH)

WHERE seminar_date = '2007-12-01';

UPDATE seminars

SET seminar_date = ADDDATE(seminar_date, 7)

WHERE seminar_date = '2007-12-15';

The first SQL statement postpones a seminar that was scheduled for December 1, 2007 to a month later (January 1, 2008). The second statement postpones the seminar on December 15 to December 22, seven days later.

Name

ADDTIME()

Synopsis

ADDTIME(datetime, datetime)

This function returns the date and time for a given string or column (in time or datetime format), incremented by the time given as the second argument. If a negative number is given, the time is subtracted. In this case, the function is the equivalent of SUBTIME(). This function is available as of version 4.1.1 of MySQL. Here is an example:

SELECT NOW( ) AS Now,

ADDTIME(NOW( ), '1:00:00.00') AS 'Hour Later';

+---------------------+---------------------+

| Now | Hour Later |

+---------------------+---------------------+

| 2007-01-11 23:20:30 | 2007-01-12 00:20:30 |

+---------------------+---------------------+

Notice that the hour is increased by one, and because the time is near midnight, the function causes the date to be altered by one day as well. To increase the date, add the number of days before the time (separated by a space) like so:

SELECT NOW( ) AS Now,

ADDTIME(NOW( ), '30 0:0:0') AS 'Thirty Days Later';

+---------------------+---------------------+

| Now | Thirty Days Later |

+---------------------+---------------------+

| 2007-01-11 23:20:30 | 2007-02-10 23:20:30 |

+---------------------+---------------------+

Name

CONVERT_TZ()

Synopsis

CONVERT_TZ(datetime, time_zone, time_zone)

This function converts a given date and time from the first time zone given to the second. It requires time zone tables to be installed in the mysql database. If they are not already installed on your system, go to MySQL AB’s web site (http://dev.mysql.com/downloads/timezones.html) to download the tables. Copy them into the mysql subdirectory of the data directory of MySQL. Change the ownership to the mysql system user and change the user permissions with system commands such as chown and chmod, and restart the server. This function is available as of version 4.1.3 of MySQL. Here is an example:

SELECT NOW() AS 'New Orleans',

CONVERT_TZ(NOW(), 'US/Central', 'Europe/Rome')

e')

AS Milan;

+---------------------+---------------------+

| New Orleans | Milan |

+---------------------+---------------------|

| 2007-03-12 20:56:15 | 2007-03-13 02:56:15 |

+---------------------+---------------------+

This example retrieves the current time of the server, which for the sake of this example is located in New Orleans, and converts this time to the time in Milan. Notice that we’re using the named time zone of Europe/Rome. There’s isn’t a Europe/Milan choice. If a named time zone that doesn’t exist is given, a NULL value is returned for that field. To find the named time zones available, check the time_zone_name table in the mysql database:

SELECT Name

FROM mysql.time_zone_name

me

WHERE Name LIKE '%Europe%';

This will list all of the time zone names for Europe. From here, you can scan the list for one in the same zone and close to the city that you want. Incidentally, if you’re converting times with this function for tables you’ve locked, the time_zone_name table will need to be locked, too.

Name

CURDATE()

Synopsis

CURDATE()

This function returns the current system date in yyyy-mm-dd format. It will return the date in a yyyymmdd format (a numeric format) if it’s used as part of a numeric calculation. You can use the function in SELECT statements as shown here, in INSERT and UPDATE statements to set a value, or in a WHERE clause. CURDATE() is synonymous with CURRENT_DATE(); see its definition next for more details. Here is an example:

SELECT CURDATE() AS Today,

CURDATE() + 1 AS Tomorrow;

ow;

+------------+----------+

| Today | Tomorrow |

+------------+----------+

| 2007-01-15 | 20070116 |

+------------+----------+

Because the second use of the function here involves a numeric calculation, tomorrow’s date is displayed without dashes. If you only want to convert a date to the numeric format, just add 0. To keep the format the same, use this function together with a function such as ADDDATE().

Name

CURRENT_DATE()

Synopsis

CURRENT_DATE()

This function returns the current date. The usual parentheses are not required. It’s synonymous with CURDATE(). You can use either in SELECT statements, as well as INSERT and UPDATE statements to dynamically set values, or in WHERE clauses. Here is an example:

UPDATE appointment

SET appt_date = CURRENT_DATE( )

WHERE appt_id = '1250';

This statement changes the appointment date for a client who came in today unexpectedly.

Name

CURRENT_TIME()

Synopsis

CURRENT_TIME()

This function returns the current time in hh:mm:ss format. It will return the time in the hhmmss format (numeric format) if it’s used as part of a numeric calculation. The parentheses are not required. It’s synonymous with CURTIME(). Here is an example:

INSERT INTO appointments

(client_id, appt_date, start_time)

VALUES('1403', CURRENT_DATE( ), CURRENT_TIME);

In this example, we’re logging an unscheduled appointment that has just begun so that we can bill the client later. Of course, it’s easy enough to use one datetime column with the NOW() function for inserting data, and use other functions for extracting separate components later.

Name

CURRENT_TIMESTAMP()

Synopsis

CURRENT_TIMESTAMP()

This function returns the current date and time in yyyy-mm-dd hh:mm:ss format. It will return the time in a yyyymmddhhmmss format (numeric format) if it’s used as part of a numeric calculation. Parentheses aren’t required. It’s a synonym of NOW(). Here is an example:

SELECT CURRENT_TIMESTAMP() AS Now,

CURRENT_TIMESTAMP() + 10000 AS 'Hour Later';

r';

+---------------------+----------------+

| Now | Hour Later |

+---------------------+----------------+

| 2008-01-12 16:41:47 | 20080112174147 |

+---------------------+----------------+

By adding 10,000 to the current time, the hour is increased by 1 and the minutes and seconds by 0 each, and the time is displayed in the second field without dashes. This is in line with the yyyymmddhhmmss format involved in numeric calculations, with the numbers right-justified.

Name

CURTIME()

Synopsis

CURTIME()

This function returns the current system time in hh:mm:ss format. It will return the time in an hhmmss format (numeric format) if it’s used as part of a numeric calculation. This is an alias for CURRENT_TIME(). Here is an example:

SELECT CURTIME() AS Now,

CURTIME() + 10000 AS 'Hour Later';

r';

+----------+------------+

| Now | Hour Later |

+----------+------------+

| 16:35:43 | 163543 |

+----------+------------+

By adding 10,000 to the current time, this statement increases the hour by 1 and the minutes and seconds by 0 each. This is in keeping with the yyyymmddhhmmss format previously mentioned.

Name

DATE()

Synopsis

DATE(expression)

This function returns the date from a given string, value, or expression that is submitted in a date or datetime format. This function is available as of version 4.1.1 of MySQL. Here is an example:

SELECT appointment, DATE(appointment)

FROM appointments

WHERE client_id = '8639' LIMIT 1;

+---------------------+-------------------+

| appointment | DATE(appointment) |

+---------------------+-------------------+

| 2008-01-11 14:11:43 | 2008-01-11 |

+---------------------+-------------------+

In this SQL statement, the value of the appointment column, which is a DATETIME type column, is shown first. The second field is the date extracted by the function from the same column and row.

Name

DATE_ADD()

Synopsis

DATE_ADD(date, INTERVAL number type)

Using the date or datetime given, this function adds the number of intervals specified. It’s fairly synonymous with the ADDDATE() function. If none of the parameters include datetime or time factors, the results will be returned in date format. Otherwise, the results will be in datetime format. See Table 12-1 for a list of intervals permitted. Here is an example:

UPDATE appointments

SET appt_date = DATE_ADD(appt_date, INTERVAL 1 DAY)

WHERE appt_id='1202';

In this example, the appointment date is changed to its current value plus one additional day to postpone the appointment by a day. If we changed the 1 to –1, MySQL would subtract a day instead. This would make the function the equivalent of DATE_SUB().

If you leave out some numbers in the second argument, MySQL assumes that the leftmost interval factors are 0 and are just not given. In the following example, although we’re using the interval HOUR_SECOND, we’re not giving the number of hours and the function still works—assuming we don’t mean 5 hours and 30 minutes later. MySQL assumes here that we mean '00:05:30' and not '05:30:00':

SELECT NOW( ) AS 'Now',

DATE_ADD(NOW( ), INTERVAL '05:30' HOUR_SECOND)

AS 'Later';

+---------------------+---------------------+

| Now | Later |

+---------------------+---------------------+

| 2007-03-14 10:57:05 | 2007-03-14 11:02:35 |

+---------------------+---------------------+

When adding the intervals MONTH, YEAR, or YEAR_MONTH to a date, if the given date is valid but the results would be an invalid date because it would be beyond the end of a month, the results are adjusted to the end of the month:

SELECT DATE_ADD('2009-01-29', INTERVAL 1 MONTH)

AS 'One Month Later';

+-----------------+

| One Month Later |

+-----------------+

| 2009-02-28 |

+-----------------+

Table 12-1 shows the intervals that may be used and how the data should be ordered. For interval values that require more than one factor, a delimiter is used and the data must be enclosed in quotes. Other delimiters may be used besides those shown in the table. For example, 'hh|mm|ss'could be used for HOUR_SECOND. In case you hadn’t noticed, the names for intervals involving more than two time factors use the name of the first and last factor (e.g., DAY_MINUTE and not DAY_HOUR_MINUTE). Keep that in mind when trying to remember the correct interval.

Table 12-1. DATE_ADD() intervals and formats

INTERVAL

Format for given values

DAY

dd

DAY_HOUR

'dd hh'

DAY_MICROSECOND

'dd.nn'

DAY_MINUTE

'dd hh:mm'

DAY_SECOND

'dd hh:mm:ss'

HOUR

hh

HOUR_MICROSECOND

'hh.nn'

HOUR_MINUTE

'hh:mm'

HOUR_SECOND

'hh:mm:ss'

MICROSECOND

nn

MINUTE

mm

MINUTE_MICROSECOND

'mm.nn'

MINUTE_SECOND

'mm:ss'

MONTH

mm

QUARTER

qq

SECOND

ss

SECOND_MICROSECOND

'ss.nn'

WEEK

ww

YEAR

yy

YEAR_MONTH

'yy-mm'

Name

DATE_FORMAT()

Synopsis

DATE_FORMAT(date, 'format_code')

This function returns a date and time in a desired format, based on formatting codes listed within quotes for the second argument of the function. Here is an example:

SELECT DATE_FORMAT(appointment, '%W - %M %e, %Y at %r')

AS 'Appointment'

FROM appointments

WHERE client_id = '8392'

AND appointment > CURDATE( );

+---------------------------------------+

| Appointment |

+---------------------------------------+

| Monday - June 16, 2008 at 01:00:00 PM |

+---------------------------------------+

Using the formatting codes, we’re specifying in this example that we want the name of the day of the week (%W) followed by a dash and then the date of the appointment in a typical U.S. format (%M %e, %Y), with the month name and a comma after the day. We’re ending with the word “at” followed by the full nonmilitary time (%r). The results are returned as a binary string.

As of MySQL version 5.1.15, a string is returned along with the character set and collation of the string, taken from the character_set_connection and the collation_connection system variables. This allows the function to return non-ASCII characters. Here is an example of this function:

SELECT NOW( ),

DATE_FORMAT(NOW( ), '%M') AS 'Month in Hebrew';

+---------------------+-----------------+

| Now | Month in Hebrew |

+---------------------+-----------------+

| 2008-03-14 12:00:24 | מרץ |

+---------------------+-----------------+

In this example, of course, the client and server were set to display Hebrew characters. Also, the server variable lc_time_names was set to Hebrew (he_IL) so as to return the Hebrew word for March. See MySQL’s documentation page on MySQL Server Locale Support(http://dev.mysql.com/doc/refman/5.1/en/locale-support.html) for a list of locale values available for time names.

Table 12-2 contains a list of all the formatting codes you can use with DATE_FORMAT(). You can also use these codes with TIME_FORMAT() and EXTRACT().

Table 12-2. DATE_FORMAT() format codes and resulting formats

Code

Description

Results

%%

A literal '%'

%a

Abbreviated weekday name

(Sun...Sat)

%b

Abbreviated month name

(Jan...Dec)

%c

Month, numeric

(1...12)

%d

Day of the month, numeric

(00...31)

%D

Day of the month with English suffix

(1st, 2nd, 3rd, etc.)

%e

Day of the month, numeric

(0...31)

%f

Microseconds, numeric

(000000...999999)

%h

Hour

(01...12)

%H

Hour

(00...23)

%i

Minutes, numeric

(00...59)

%I

Hour

(01...12)

%j

Day of the year

(001...366)

%k

Hour

(0...23)

%l

Hour

(1...12)

%m

Month, numeric

(01...12)

%M

Month name

(January...December)

%p

A.M. or P.M.

A.M. or P.M.

%r

Time, 12-hour

(hh:mm:ss [AM|PM])

%s

Seconds

(00...59)

%S

Seconds

(00...59)

%T

Time, 24-hour

(hh:mm:ss)

%u

Week, where Monday is the first day of the week

(0...52)

%U

Week, where Sunday is the first day of the week

(0...52)

%v

Week, where Monday is the first day of the week; used with %x

(1...53)

%V

Week, where Sunday is the first day of the week; used with %X

(1...53)

%w

Day of the week

(0=Sunday...6=Saturday)

%W

Weekday name

(Sunday...Saturday)

%x

Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v

(yyyy)

%X

Year for the week, where Sunday is the first day of the week, numeric, four digits; used with %V

(yyyy)

%y

Year, numeric, two digits

(yy)

%Y

Year, numeric, four digits

(yyyy)

Name

DATE_SUB()

Synopsis

DATE_SUB(date, INTERVAL number type)

Use this function to subtract from the results of a date or time data type column. See Table 12-1, under the description of DATE_ADD(), for a list of interval types. Here is an example of this function:

SELECT NOW( ) AS Today,

DATE_SUB(NOW( ), INTERVAL 1 DAY)

AS Yesterday;

+---------------------+---------------------+

| Today | Yesterday |

+---------------------+---------------------+

| 2007-05-14 14:26:54 | 2007-05-13 14:26:54 |

+---------------------+---------------------+

Notice in this example that the time remains unchanged, but the date was reduced by one day. If you place a negative sign in front of the value 1, the reverse effect will occur, giving a result of May 15 in this example. Any intervals that can be used with DATE_ADD() can also be used withDATE_SUB().

Name

DATEDIFF()

Synopsis

DATEDIFF(date, date)

This function returns the number of days of difference between the two dates given. Although a parameter may be given in date and time format, only the dates are used for determining the difference. This function is available as of version 4.1.1 of MySQL. Here is an example:

SELECT CURDATE( ) AS Today,

DATEDIFF('2008-12-25', NOW( ))

AS 'Days to Christmas';

+------------+-------------------+

| Today | Days to Christmas |

+------------+-------------------+

| 2008-03-14 | 286 |

+------------+-------------------+

Name

DAY()

Synopsis

DAY(date)

This function returns the day of the month for a given date. It’s available as of version 4.1.1 of MySQL and is synonymous with the DAYOFMONTH() function, described later. Here is an example:

SELECT DAY('2008-12-15')

AS 'Day';

+-------+

| Day |

+-------+

| 15 |

+-------+

This function is more meaningful when applied to a date column where the date is unknown before entering the SQL statement.

Name

DAYNAME()

Synopsis

DAYNAME(date)

This function returns the name of the day for the date provided. As of MySQL version 5.1.15, the lc_time_names system variable will be consulted to determine the actual set of names to use. Use the SET statement to change this variable. See MySQL’s documentation page on MySQL Server Locale Support (http://dev.mysql.com/doc/refman/5.1/en/locale-support.html) for a list of locale values available for time names. Here is an example:

SELECT appt_date AS Appointment,

DAYNAME(appt_date) AS 'Day of Week'

FROM appointments

WHERE appt_id = '1439';

+---------------------+-------------+

| Date of Appointment | Day of Week |

+---------------------+-------------+

| 2008-03-14 | Friday |

+---------------------+-------------+

SET lc_time_names = 'it_IT';

SELECT appt_date AS Appointment,

DAYNAME(appt_date) AS ''Day of Week in Italian'

FROM appointments

WHERE appt_id = '1439';

+---------------------+------------------------+

| Date of Appointment | Day of Week in Italian |

+---------------------+------------------------+

| 2008-03-14 | venerdì |

+---------------------+------------------------+

For this example, I have set character_set_client, character_set_connection, and character_set_results to utf8, and set my terminal program to UTF-8 characters. Incidentally, the day of the week here is in lowercase because this is how it’s written in Italian.

Name

DAYOFMONTH()

Synopsis

DAYOFMONTH(date)

This function returns the day of the month for the date given. If the day for the date is beyond the end of the month (e.g., '2008-02-30'), the function returns NULL along with a warning that can be retrieved with SHOW WARNINGS. Here is an example:

SELECT DAYOFMONTH('2008-02-28') AS 'A Good Day',

DAYOFMONTH('2008-02-30') AS 'A Bad Day';

+------------+-----------+

| A Good Day | A Bad Day |

+------------+-----------+

| 28 | NULL |

+------------+-----------+

1 row in set, 1 warning (0.00 sec)

SHOW WARNINGS;

+---------+------+--------------------------------------------------+

| Level | Code | Message |

+---------+------+--------------------------------------------------+

| Warning | 1292 | Truncated incorrect datetime value: '2008-02-30' |

+---------+------+--------------------------------------------------+

Prior to MySQL version 5.0.2, invalid dates such as this were permitted. The function would have returned 30 for a value of '2008-02-30'. If you wish to allow invalid dates, start your server with this line in your options file:

sql_mode = 'TRADITIONAL,ALLOW_INVALID_DATES'

Name

DAYOFWEEK()

Synopsis

DAYOFWEEK(date)

This function returns the numerical day of the week for a given date. Sunday returns a value of 1, and Saturday returns a value of 7. Here is an example:

SELECT DAYOFWEEK('2008-11-03') AS 'Day of Week',

DAYNAME('2008-11-03') AS 'Name of Day';

+-------------+-------------+

| Day of Week | Name of Day |

+-------------+-------------+

| 2 | Monday |

+-------------+-------------+

Name

DAYOFYEAR()

Synopsis

DAYOFYEAR(date)

This function returns the day of the year. January 1 would give a value of 1, and December 31 would normally be 365, except in leap years, when it would be 366. Here is an example:

SELECT DAYOFYEAR('2008-03-01') AS 'FirstDate',

DAYOFYEAR('2008-02-28') AS 'SecondDate',

(DAYOFYEAR('2008-03-01') - DAYOFYEAR('2008-02-28')) AS 'Days Apart',

DAYOFYEAR('2008-12-31') AS 'Last Day of Year';

+------------+-------------+------------+------------------+

| First Date | Second Date | Days Apart | Last Day of Year |

+------------+-------------+------------+------------------+

| 61 | 59 | 2 | 366 |

+------------+-------------+------------+------------------+

In the third field, we are using the function to calculate the number of days from the first date to the second date. Since 2008 is a leap year, the result is 2 and the last field shows 366 for the last day of the year.

Name

EXTRACT()

Synopsis

EXTRACT(type FROM expression)

This function extracts date or time information from a date or a datetime expression in the format type requested. The acceptable types are the same as the intervals for DATE_ADD(). See Table 12-1 earlier in this chapter under that function for a list of intervals permitted. Here is an example:

SELECT NOW( ) AS 'Time Now',

EXTRACT(HOUR_MINUTE FROM NOW( )) AS "Now in 'hhmm' format";

+---------------------+----------------------+

| Time Now | Now in 'hhmm' format |

+---------------------+----------------------+

| 2008-03-14 20:36:04 | 2036 |

+---------------------+----------------------+

Name

FROM_DAYS()

Synopsis

FROM_DAYS(value)

This function returns the date based on the number of days given, which are from the beginning of the currently used standard calendar. Problems occur for dates before 1582, when the Gregorian calendar became the standard. The opposite of this function is TO_DAYS(). Here is an example:

SELECT FROM_DAYS((365.25*2008))

AS 'Start of 2008?', FROM_DAYS(366);

+----------------+

| Start of 2008? |

+----------------+

| 2008-01-16 |

+----------------+

Assuming that there are 365.25 days in a year on average (allowing for the leap year), you would think that multiplying that factor by 2008 would give a result of January 1, 2008, but it doesn’t because of the calendar change centuries ago. This function is possibly useful for comparing dates and displaying the results in a readable format. However, since there are many other functions available in MySQL, its usefulness is fairly diminished. Here is an example:

SELECT CURDATE( ) As 'Now',

TO_DAYS(NOW( )) AS 'Days since Day 0',

FROM_DAYS(TO_DAYS(NOW( )) + 7) AS '7 Days from Now',

ADDDATE(CURDATE( ), 7) AS 'Simpler Method';

+------------+------------------+-----------------+----------------+

| Now | Days since Day 0 | 7 Days from Now | Simpler Method |

+------------+------------------+-----------------+----------------+

| 2007-03-14 | 733114 | 2007-03-21 | 2007-03-21 |

+------------+------------------+-----------------+----------------+

Name

FROM_UNIXTIME()

Synopsis

FROM_UNIXTIME(unix_timestamp[, format])

This function returns the date based on Unix time, which is the number of seconds since January 1, 1970, Greenwich Mean Time (GMT), with 12:00:01 being the first second of Unix time (the epoch). The second, optional argument formats the results using the formatting codes fromDATE_FORMAT(). The function returns the date and time in the yyyy-mm-dd hh:mm:ss format, unless it’s part of a numeric expression. Then it returns the data in the yyyymmdd format. Here is an example:

SELECT FROM_UNIXTIME(0) AS 'My Epoch Start',

UNIX_TIMESTAMP( ) AS 'Now in Unix Terms',

FROM_UNIXTIME(UNIX_TIMESTAMP( )) AS 'Now in Human Terms';

+---------------------+-------------------+---------------------+

| My Epoch Start | Now in Unix Terms | Now in Human Terms |

+---------------------+-------------------+---------------------+

| 1969-12-31 18:00:00 | 1173928232 | 2007-03-14 22:10:32 |

+---------------------+-------------------+---------------------+

Here we’re selecting the date based on zero seconds since the start of Unix time. The results are off by six hours because the server’s not located in the GMT zone. This function is typically used on columns whose values were derived from UNIX_TIMESTAMP(), as shown in the third field of the example.

Name

GET_FORMAT()

Synopsis

GET_FORMAT(data_type, standard)

This function returns the format for a given data type, based on the standard given as the second argument. The format codes returned are the same codes used by the DATE_FORMAT() function. The data type may be DATE, TIME, DATETIME, or TIMESTAMP, and the format type may be EUR,INTERNAL, ISO, JIS, or USA. This function is available as of version 4.1.1 of MySQL. The TIMESTAMP data type isn’t acceptable until version 4.1.4.

Here’s an example using the function that returns the USA format:

SELECT GET_FORMAT(DATE, 'USA') AS 'US Format',

GET_FORMAT(DATE, 'EUR') AS 'European Format';

+-----------+-----------------+

| US Format | European Format |

+-----------+-----------------+

| %m.%d.%Y | %d.%m.%Y |

+-----------+-----------------+

I wouldn’t say that using the period as the separator is very American, but the order of day followed by month is in keeping with American standards, and the day preceding the month is European. You can hand off the results of the function to DATE_FORMAT() to format the value of a date column like so:

SELECT appointment,

DATE_FORMAT(appointment, GET_FORMAT(DATE, 'USA'))

AS 'Appointment'

WHERE apt_id = '8382';

+-------------+-------------+

| appointment | Appointment |

+-------------+-------------+

| 2008-03-15 | 03.15.2008 |

+-------------+-------------+

Table 12-3 lists the results for the different combinations. The ISO standard refers to ISO 9075. The data type of TIMESTAMP is not listed because the results are the same as DATETIME.

Table 12-3. DATE_FORMAT arguments and their results

Combination

Results

DATE, 'EUR'

%d.%m.%Y

DATE, 'INTERNAL'

%Y%m%d

DATE, 'ISO'

%Y-%m-%d

DATE, 'JIS'

%Y-%m-%d

DATE, 'USA'

%m.%d.%Y

TIME, 'EUR'

%H.%i.%S

TIME, 'INTERNAL'

%H%i%s

TIME, 'ISO'

%H:%i:%s

TIME, 'JIS'

%H:%i:%s

TIME, 'USA'

%h:%i:%s %p

DATETIME, 'EUR'

%Y-%m-%d-%H.%i.%s

DATETIME, 'INTERNAL'

%Y%m%d%H%i%s

DATETIME, 'ISO'

%Y-%m-%d %H:%i:%s

DATETIME, 'JIS'

%Y-%m-%d %H:%i:%s

DATETIME, 'USA'

%Y-%m-%d-%H.%i.%s

Name

HOUR()

Synopsis

HOUR(time)

This function returns the hour for the time given. For columns containing the time of day (e.g., DATETIME), the range of results will be from 0 to 23. For TIME data type columns that contain data not restricted to day limits, this function may return values greater than 23. Here is an example:

SELECT appt_id, appointment,

HOUR(appointment) AS 'Hour of Appointment'

FROM appointments

WHERE client_id = '3992'

AND appointment > CURDATE( );

+---------+---------------------+---------------------+

| appt_id | appointment | Hour of Appointment |

+---------+---------------------+---------------------+

| 8393 | 2008-03-15 13:00:00 | 13 |

+---------+---------------------+---------------------+

This statement is selecting the upcoming appointment for a particular client. The hour is returned in military time (i.e., 13 is 1 P.M.).

Name

LAST_DAY()

Synopsis

LAST_DAY(date)

This function returns the date of the last day of the month for a given date or datetime value. NULL is returned for invalid dates. It’s available as of version 4.1.1 of MySQL. Here is an example:

SELECT LAST_DAY('2008-12-15')

AS 'End of Month';

+--------------+

| End of Month |

+--------------+

| 2008-12-31 |

+--------------+

There is no FIRST_DAY() function at this time. However, you can use LAST_DAY() in conjunction with a couple of other functions to return the first day of the month:

SELECT CURDATE( ) AS 'Today',

ADDDATE(LAST_DAY(SUBDATE(CURDATE(), INTERVAL 1 MONTH)), 1)

AS 'First Day of Month';

+------------+--------------------+

| Today | First Day of Month |

+------------+--------------------+

| 2008-06-18 | 2008-06-01 |

+------------+--------------------+

In this example, we are subtracting one month from the results of CURDATE() to get the same day last month. From there, we’re using LAST_DAY() to find the last day of last month. Then ADDDATE() is employed to add one day to the results, to find the first day of the month after last month, that is to say, the current month. This method adjusts for dates in January that would involve a previous year.

Name

LOCALTIME()

Synopsis

LOCALTIME()

This function returns the current system date in yyyy-mm-dd hh:mm:ss format. When part of a calculation, the results are in the numeric format of yyyymmddhhmmss.nnnnnn, which has placeholders for macroseconds. The parentheses are not required. It’s available as of version 4.0.6 of MySQL and is synonymous with LOCALTIMESTAMP() and NOW(). Here is an example:

SELECT LOCALTIME( ) AS 'Local Time',

LOCALTIME( ) + 0 AS 'Local Time as Numeric';

+---------------------+-----------------------+

| Local Time | Local Time as Numeric |

+---------------------+-----------------------+

| 2007-03-15 01:53:16 | 20070315015316.000000 |

+---------------------+-----------------------+

Name

LOCALTIMESTAMP()

Synopsis

LOCALTIMESTAMP()

This function returns the current system date in yyyy-mm-dd hh:mm:ss format. When part of a calculation, the results are in the numeric format of yyyymmddhhmmss.nnnnnn, which has placeholders for macroseconds. It’s synonymous with LOCALTIMESTAMP() and NOW(). Here is an example:

UPDATE appointments

SET end_time = LOCALTIME( )

WHERE appt_id = '8839';

Name

MAKEDATE()

Synopsis

MAKEDATE(year, days)

This function determines the date requested from the start of the given year, by adding the number of days given in the second argument. It returns the date in the yyyy-mm-dd format. It returns NULL if a value given for days is not greater than 0. It will accept more than a year’s worth of days, though. It just returns a date into the next year or whatever year is appropriate, based on however many days the result is from the beginning of the year given. This function is available as of version 4.1.1 of MySQL. Here is an example:

SELECT MAKEDATE(2009, 1) AS 'First Day',

MAKEDATE(2009, 365) AS 'Last Day',

MAKEDATE(2009, 366) AS 'One More Day';

+------------+------------+--------------+

| First Day | Last Day | One More Day |

+------------+------------+--------------+

| 2009-01-01 | 2009-12-31 | 2010-01-01 |

+------------+------------+--------------+

Name

MAKETIME()

Synopsis

MAKETIME(hour, minute, second)

This function converts a given hour, minute, and second to hh:mm:ss format. It returns NULL if the value for the minute or the second values are greater than 59. It will accept an hour value greater than 24, though. This function is available as of version 4.1.1 of MySQL. Here is an example:

SELECT MAKETIME(14, 32, 5)

AS Time;

+----------+

| Time |

+----------+

| 14:32:05 |

+----------+

Name

MICROSECOND()

Synopsis

MICROSECOND(time)

This function extracts the microseconds value of a given time. It displays the resulting number in six characters, padded with zeros to the right. When a date or datetime is given that does not include a specific value for microseconds, a value of zero microseconds is assumed. Therefore, 000000 is returned. This function is available as of version 4.1.1 of MySQL. Here is an example:

SELECT MICROSECOND('2008-01-11 19:28:45.82')

AS 'MicroSecond';

+--------------+

| MicroSecond |

+--------------+

| 820000 |

+--------------+

Name

MINUTE()

Synopsis

MINUTE(time)

This function returns the minute value (0–59) of a given time. Here is an example:

SELECT CONCAT(HOUR(appointment), ':',

MINUTE(appointment)) AS 'Appointment'

FROM appointments

WHERE client_id = '3992'

AND appointment > CURDATE( );

+-------------+

| Appointment |

+-------------+

| 13:30 |

+-------------+

This statement is using the string function CONCAT() to paste together the hour and the minute, with a colon as a separator. Of course, a function such as DATE_FORMAT() would be a better choice for such a task. If an invalid time is given (e.g., minutes or seconds in excess of 59), NULL is returned and a warning issued:

SELECT MINUTE('13:60:00') AS 'Bad Time',

MINUTE('13:30:00') AS 'Good Time';

+----------+-----------+

| Bad Time | Good Time |

+----------+-----------+

| NULL | 30 |

+----------+-----------+

1 row in set, 1 warning (0.00 sec)

SHOW WARNINGS;

+---------+------+--------------------------------------------+

| Level | Code | Message |

+---------+------+--------------------------------------------+

| Warning | 1292 | Truncated incorrect time value: '13:60:00' |

+---------+------+--------------------------------------------+

Name

MONTH()

Synopsis

MONTH(date)

This function returns the numeric value of the month (0–12) for the date provided. Since a date column can contain a zero value (e.g., '0000-00-00'), the function will return 0 for those situations. However, for nonzero invalid dates given, NULL is returned. Here is an example:

SELECT appointment AS 'Appointment',

MONTH(appointment) AS 'Month of Appointment'

FROM appointments

WHERE client_id = '8302'

AND appointment > CURRDATE( );

+-------------+----------------------+

| Appointment | Month of Appointment |

+-------------+----------------------+

| 2008-06-15 | 6 |

+-------------+----------------------+

This SQL statement is retrieving the month of any appointments after the current date for a particular client. There’s only one appointment, and it’s in June.

Name

MONTHNAME()

Synopsis

MONTHNAME(date)

This function returns the name of the month for the date provided. As of version 5.1.15 of MySQL, the lc_time_names system variable is used to determine the actual set of names to use. Use the SET statement to change this variable. See the MySQL documentation page on MySQL Server Locale Support (http://dev.mysql.com/doc/refman/5.1/en/locale-support.html) for a list of locale values available for time names. Here is an example:

SELECT appointment AS 'Appointment',

MONTHNAME(appointment) AS 'Month of Appointment'

FROM appointments

WHERE client_id = '8302'

AND appointment > NOW( );

+-------------+----------------------+

| Appointment | Month of Appointment |

+-------------+----------------------+

| 2008-03-15 | March |

+-------------+----------------------+

SET lc_time_names = 'it_IT';

+-------------+----------------------+

| Appointment | Month of Appointment |

+-------------+----------------------+

| 2008-03-15 | marzo |

+-------------+----------------------+

In this example, the client has only one appointment after the current date, and it’s in March. After setting the lc_time_names variable to 'it_I'’ (i.e., italian, Italy), the results returned for the same SQL statement are given in Italian. You can use this function in conjunction with a function such as CONCAT() to paste the results into other text or to create a style you prefer:

SELECT CONCAT('Il tuo appuntamento è in ', MONTHNAME(appointment), '.')

AS 'Reminder'

FROM appointments

WHERE client_id = '8302'

AND appointment > NOW( );

+---------------------------------+

| Reminder |

+---------------------------------+

| Il tuo appuntamento è in marzo. |

+---------------------------------+

Name

NOW()

Synopsis

NOW()

This function returns the current date and time. The format returned is yyyy-mm-dd hh:mm:ss.nnnnnn, unless the function is used in a numeric calculation. Then it will return the data in a yyyymmdd format. It’s synonymous with LOCALTIME() and LOCALTIMESTAMP(). Here is an example:

SELECT NOW( ) AS Now,

NOW( ) + 105008 AS '1 hour, 50 min., 8 sec. Later';

+---------------------+-------------------------------+

| Now | 1 hour, 50 min., 8 sec. Later |

+---------------------+-------------------------------+

| 2007-03-18 20:08:30 | 20070318305838.000000 |

+---------------------+-------------------------------+

By adding 105,008 to the current time, the hour is increased by 1, the minutes by 50, and the seconds by 8, and the time is displayed in the second field without dashes. Notice that the results show the hours to be 30 now and not 6, and the date wasn’t adjusted. Raw adding of time is usually not a good alternative to functions such as DATE_ADD() or TIME_ADD().

The NOW() function is similar to the SYSDATE() function in that they both return the current datetime in the same format. However, the NOW() function returns the time the SQL statement began, whereas SYSDATE() returns the time the function was invoked. This can lead to differences when long triggers or stored procedures run; an embedded SYSDATE() will then reflect a later time than NOW(). For this reason, there are potential problems using SYSDATE() with regard to replication. See the description of SYSDATE() later in this chapter for more information:

SELECT NOW( ) AS 'Start',

SLEEP(5) AS 'Pause',

NOW( ) AS 'Middle But Same',

SYSDATE( ) AS 'End';

+---------------------+-------+---------------------+---------------------+

| Start | Pause | Middle But Same | End |

+---------------------+-------+---------------------+---------------------+

| 2008-06-15 11:02:41 | 0 | 2008-06-15 11:02:41 | 2008-06-15 11:02:46 |

+---------------------+-------+---------------------+---------------------+

1 row in set (5.27 sec)

MySQL executes the elements of a SELECT statement from left to right, so the Start field is determined first. The SLEEP() function instructs the server to pause the execution of the SQL statement by the amount of seconds given. After this, the third element is executed. As you can see, the results for that third field are the same as the first because NOW() returns the starting time. However, in the fourth field, SYSDATE() returns the time it was executed, five seconds after the start. This may not seem like much of a difference between the functions, but there may be situations where it matters. In particular, it may matter with SQL statements, triggers, or stored procedures that take a long time to finish executing.

Name

PERIOD_ADD()

Synopsis

PERIOD_ADD(yearmonth, number)

This function adds a specified number of months to a period, which is a string containing only the year and month in either yyyymm or yymm format. Here is an example:

SELECT CURDATE( ),

EXTRACT(YEAR_MONTH FROM CURDATE( ))

AS 'Current Period',

PERIOD_ADD(EXTRACT(YEAR_MONTH FROM CURDATE( )), 1)

AS 'Next Accounting Period';

+------------+----------------+------------------------+

| CURDATE( ) | Current Period | Next Accounting Period |

+------------+----------------+------------------------+

| 2008-12-15 | 200812 | 200901 |

+------------+----------------+------------------------+

Functions such as this one are particularly useful when you are building a program and need to design an SQL statement that will account for accounting periods that roll into the following year.

Name

PERIOD_DIFF()

Synopsis

PERIOD_DIFF(yearmonth,yearmonth)

This function returns the number of months between the periods given. The periods given must be in string format and contain only the year and month, in either yyyymm or yymm format. Here is an example:

SELECT appointment AS 'Date of Appointment',

CURDATE( ) AS 'Current Date',

PERIOD_DIFF(

EXTRACT(YEAR_MONTH FROM appointment),

EXTRACT(YEAR_MONTH FROM CURDATE( ))

) AS 'Accounting Periods Apart';

+------------------+--------------+-----------------------------+

| Last Appointment | Current Date | Accounting Periods Ellapsed |

+------------------+--------------+-----------------------------+

| 2008-11-15 | 2009-01-15 | -2 |

+------------------+--------------+-----------------------------+

This SQL statement determines that it has been two months since the client’s last appointment. If you want the results not to contain a negative, either switch the order of the periods or wrap the PERIOD_DIFF() within ABS(). The PERIOD_DIFF() function takes into account that the periods are in different years. But it doesn’t work on standard date columns, so you have to put them into the proper string format as shown here with a function such as EXTRACT().

Name

QUARTER()

Synopsis

QUARTER(date)

This function returns the number of the quarter (1–4) for the date provided. The first quarter (i.e., the first three months) of each year has a value of 1. Here is an example:

SELECT COUNT(appointment)

AS 'Appts. Last Quarter'

FROM appointments

WHERE QUARTER(appointment) = (QUARTER(NOW( )) - 1)

AND client_id = '7393';

+---------------------+

| Appts. Last Quarter |

+---------------------+

| 16 |

+---------------------+

In this example, MySQL calculates the total number of appointments for a particular client that occurred before the current quarter. The flaw in this SQL statement is that it doesn’t work when it’s run during the first quarter of a year. In the first quarter, the calculation on the fourth line would produce a quarter value of 0. This statement also doesn’t consider appointments in previous quarters of previous years. To solve these problems, we could set up user-defined variables for the values of the previous quarter and for its year:

SET @LASTQTR:=IF((QUARTER(CURDATE( ))-1) = 0, 4, QUARTER(CURDATE( ))-1);

SET @YR:=IF(@LASTQTR = 4, YEAR(NOW( ))-1, YEAR(NOW( )));

SELECT COUNT(appointment) AS 'Appts. Last Quarter'

FROM appointments

WHERE QUARTER(appointment) = @LASTQTR

AND YEAR(appointment) = @YR

AND client_id = '7393';

In the first SQL statement here, we use an IF statement to test whether reducing the quarter by 1 would yield a 0 value. If so, we’ll set the user variable for the last quarter to 4. In the second statement, we establish the year for the last quarter based on the value determined for @LASTQTR. The last SQL statement selects rows and counts them where the QUARTER() function yields a value equal to the @LASTQTR variable and where the YEAR() function yields a value equal to the @YR variable based on the appointment date, and where the client is the one for which we are running the statement.

Name

SEC_TO_TIME()

Synopsis

SEC_TO_TIME(seconds)

This function returns the period for a given number of seconds in the format hh:mm:ss. It will return the time in hhmmss format if it’s used as part of a numeric calculation. Here is an example:

SELECT SEC_TO_TIME(3600) AS 'Time Format',

SEC_TO_TIME(3600) + 0 AS 'Numeric Format';

+-------------+----------------+

| Time Format | Numeric Format |

+-------------+----------------+

| 01:00:00 | 10000.000000 |

+-------------+----------------+

We’ve given a value of 3,600 seconds, which the function formats to show as 1 hour in the first field. The next field shows the same results, but in numeric format and with microseconds included. If the number of seconds exceeds 86,400, or 1 day’s worth, the value for hours will result in an amount greater than 23 and will not be reset back to 0.

Name

SECOND()

Synopsis

SECOND(time)

This function returns the seconds value (0–59) for a given time. Here is an example:

SELECT NOW( ), SECOND(NOW( ));

+---------------------+----------------+

| NOW( ) | SECOND(NOW( )) |

+---------------------+----------------+

| 2009-05-09 14:56:11 | 11 |

+---------------------+----------------+

The first field generated shows the time that this statement was entered, using the NOW() function. The second field displays only the seconds value for the results of NOW().

Name

SLEEP()

Synopsis

SLEEP(seconds)

This function pauses the execution of an SQL statement in which it is given for the number of seconds given. It returns 0 in the results if successful; 1 if not. This function became available as of version 5.0.12 of MySQL. It’s not exactly a time and date function, but it’s included here due to it’s true time aspects. Here is an example:

SELECT SYSDATE( ) AS 'Start',

SLEEP(5) AS 'Pause',

SYSDATE( ) AS 'End';

+---------------------+-------+---------------------+

| Start | Pause | End |

+---------------------+-------+---------------------+

| 2008-07-16 13:50:20 | 0 | 2008-07-16 13:50:25 |

+---------------------+-------+---------------------+

1 row in set (5.13 sec)

The SYSDATE() function returns the time it is executed, not necessarily the time the statement started or finished. You can see that the time in the first field is different by five seconds from the results in the third field due to the use of SLEEP(). Notice also that the statement took a little over five seconds to execute.

If you type Ctrl-C one time before an SQL statement containing SLEEP() is completed, it will return 1 for the SLEEP() field and MySQL will then go on to execute the rest of the SQL statement. In that case, the third field in the previous example would show less than a five-second difference from the first.

Name

STR_TO_DATE()

Synopsis

STR_TO_DATE(datetime, 'format_code')

This function returns the date and time of a given string for a given format. The function takes a string containing a date or time, or both. To specify the format of the string returned, a formatting code needs to be provided in the second argument. The formatting codes are the same codes used by the DATE_FORMAT() function; see its definition for a list of those formats. This function is available as of version 4.1.1 of MySQL. Here is an example:

SELECT STR_TO_DATE(

'January 15, 2008 1:30 PM',

'%M %d, %Y %h:%i %p'

) AS Anniversary;

+---------------------+

| Anniversary |

+---------------------+

| 2008-01-15 13:30:00 |

+---------------------+

To retrieve a return value suitable for insertion into a date or time column, use '%Y-%m-%d' for a date column and '%h:%i:%s' for a time column.

Name

SUBDATE()

Synopsis

SUBDATE(date, INTERVAL value type)

SUBDATE(date days)

Use this function to subtract a date or time interval from the results of a DATE or TIME data type column. It’s an alias for DATE_SUB(). If a negative value is given, the interval specified is added instead of subtracted. This is the equivalent of ADDDATE(). See Table 12-1 underDATE_ADD() earlier in this chapter for a list of intervals permitted. Here is an example:

SELECT SUBDATE(NOW( ), 1) AS 'Yesterday',

SUBDATE(NOW( ), INTERVAL -1 DAY) AS 'Tomorrow';

+---------------------+---------------------+

| Yesterday | Tomorrow |

+---------------------+---------------------+

| 2008-05-09 16:11:56 | 2008-05-11 16:11:56 |

+---------------------+---------------------+

As of version 4.1 of MySQL, when subtracting days you can just give the number of days for the second argument (i.e., just 1 instead of INTERVAL 1 DAY).

Name

SUBTIME()

Synopsis

SUBTIME(datetime, datetime_value)

This function returns the date and time for the given string or column decreased by the time given as the second argument (d hh:mm:ss). If a negative number is given, the time is added and the function is the equivalent of ADDTIME(). This function is available as of version 4.1.1 of MySQL. Here is an example:

SELECT NOW( ) AS Now,

SUBTIME(NOW( ), '1:00:00.000000') AS 'Hour Ago';

+---------------------+---------------------+

| Now | Hour Ago |

+---------------------+---------------------+

| 2008-01-12 00:54:59 | 2008-01-11 23:54:59 |

+---------------------+---------------------+

Notice that the hour is decreased by one, and because the time is just after midnight, the function causes the date to be altered by one day as well. If either argument is given with a microsecond value other than all zeros, the results will include microseconds. To decrease the date, give the number of days before the time (separated by a space) like so:

SELECT NOW( ) AS Now,

SUBTIME(NOW( ), '30 0:0.0') AS 'Thirty Days Ago';

+---------------------+---------------------+

| Now | Thirty Days Ago |

+---------------------+---------------------+

| 2008-01-12 00:57:04 | 2007-12-13 00:57:04 |

+---------------------+---------------------+

Name

SYSDATE()

Synopsis

SYSDATE()

This function returns the system date at the time it is executed. It will return the date and time in the yyyy-mm-dd hh:mm:ss format, but will return the data in the yyyymmddhhmmss format if it’s used as part of a numeric calculation. It will display the microseconds value if the calculation involves a microseconds value. Here is an example:

SELECT SYSDATE( ),

SYSDATE( ) + 0 AS 'Numeric Format';

+---------------------+----------------+

| SYSDATE( ) | Numeric Format |

+---------------------+----------------+

| 2008-03-15 23:37:38 | 20080315233738 |

+---------------------+----------------+

This function is similar to the NOW() function in that they both return the current datetime and in the same format. However, the NOW() function returns the time when the SQL statement began, whereas SYSDATE() returns the time the function was invoked. See the definition of NOW()earlier in this chapter for an example of this situation and its significance.

If you’re using replication, the binary log will include SET TIMESTAMP entries, so if you restore a database from the binary log, values from NOW() will be adjusted to the same times as when the original SQL statements were executed. SYSDATE() entries are unaffected by these SET TIMESTAMP entries:

SET @yesterday = UNIX_TIMESTAMP(SUBDATE(SYSDATE( ), 1));

SELECT FROM_UNIXTIME(@yesterday);

+---------------------------+

| FROM_UNIXTIME(@yesterday) |

+---------------------------+

| 2008-03-17 00:19:17 |

+---------------------------+

SET TIMESTAMP = @yesterday;

SELECT NOW( ), SYSDATE( );

+---------------------+---------------------+

| NOW( ) | SYSDATE( ) |

+---------------------+---------------------+

| 2008-03-17 00:19:17 | 2008-03-16 00:22:53 |

+---------------------+---------------------+

These statements are more involved than necessary, but they help illustrate my point. In the first SQL statement, we use the SET statement to set up a user variable to hold the date and time of yesterday. To change the TIMESTAMP variable, we need the new datetime in the Unix time format, so we use UNIX_TIMESTAMP(). Within that function, we use SUBDATE() to get the datetime for one day before. The second statement is just so we can see the value of the user variable. With the third statement, we set the system variable to the value of the user variable we created. The result is that when we run the last SQL statement—the SELECT() with both NOW() and SYSDATE()—we can see that the results are different by the one day and also a few seconds. The difference is that the value for NOW() is locked because we set the TIMESTAMP variable.

If you’re replicating, you may not want to use SYSDATE() for setting values, as their results won’t be replicated if you restore the data later. It is possible to resolve this problem by starting the server with the --sysdate-is-now option. This will cause SYSDATE() to function the same asNOW().

Name

TIME()

Synopsis

TIME(time)

This function returns the time from a given string or column containing date and time data. It’s available as of version 4.1.1 of MySQL. Here is an example:

SELECT NOW( ), As Now,

TIME(NOW( )) AS 'Time only';

+---------------------+-----------+

| Now | Time only |

+---------------------+-----------+

| 2008-03-17 00:19:17 | 00:19:17 |

+---------------------+-----------+

Name

TIME_FORMAT()

Synopsis

TIME_FORMAT(time, format_code)

This function returns the time value of the time element provided and formats it according to formatting codes given as the second argument. See Table 12-1 under the DATE_FORMAT() function earlier in this chapter for formatting codes, but only those related to time values. This function will return NULL or 0 for nontime formatting codes. Here is an example:

SELECT TIME_FORMAT(appointment, '%l:%i %p')

AS 'Appt. Time' FROM appointments

WHERE client_id = '8373'

AND appointment > SYSDATE( );

+------------+

| Appt. Time |

+------------+

| 1:00 PM |

+------------+

Name

TIME_TO_SEC()

Synopsis

TIME_TO_SEC(time)

This function returns the number of seconds that the given time represents. It’s the inverse of SEC_TO_TIME(). Here is an example:

SELECT TIME_TO_SEC('01:00')

AS 'Seconds to 1 a.m.';

+-------------------+

| Seconds to 1 a.m. |

+-------------------+

| 3600 |

+-------------------+

Here, we calculate the number of seconds up until 1 A.M. (i.e., 60 seconds times 60 minutes), or one hour into the day.

Name

TIMEDIFF()

Synopsis

TIMEDIFF(time, time)

This function returns the time difference between the two times given. Although the arguments may be given in time or datetime format, both arguments must be of the same data type. Otherwise, NULL will be returned. Microseconds may be included in the values given. They will be returned when given and if the result is not zero microseconds. This function is available as of version 4.1.1 of MySQL. Here is an example:

SELECT appointment AS Appointment,

NOW( ) AS 'Time Now',

TIMEDIFF(appointment, NOW( )) AS 'Time Remaining'

FROM appointments

WHERE rec_id='3783';

+--------------------+--------------------+----------------+

| Appointment | Time Now | Time Remaining |

+--------------------+--------------------+----------------+

| 2008-01-11 10:30:00| 2008-01-10 22:28:09| 12:01:51 |

+--------------------+--------------------+----------------+

Name

TIMESTAMP()

Synopsis

TIMESTAMP(date, time)

This function merges the date and time from given strings or columns that contain date and time data separately; the result is returned in yyyy-mm-dd hh:mm:ss format. If only the date or only the time is given, the function will return zeros for the missing parameter. It’s available as of version 4.1.1 of MySQL. Here is an example:

SELECT TIMESTAMP(appt_date, appt_time) AS 'Appointment'

FROM appointments LIMIT 1;

+---------------------+

| Appointment |

+---------------------+

| 2008-07-16 11:13:41 |

+---------------------+

Name

TIMESTAMPADD()

Synopsis

TIMESTAMPADD(interval, number, datetime)

This function adds the given number of intervals of time to the given date or time. Intervals that are accepted by this function are: FRAC_SECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, and YEAR. For compatibility with other systems, you can add the SQL_TSI_ prefix to these interval names (e.g., SQL_TSI_YEAR for YEAR). This function is available as of version 5.0.0 of MySQL and is similar to DATE_ADD(), but the list of intervals accepted is not exactly the same. Here is an example:

UPDATE appointments

SET appointment = TIMESTAMPADD(HOUR, 1, appointment)

WHERE appt_id = '8930';

In this example, an appointment is set to an hour later.

Name

TIMESTAMPDIFF()

Synopsis

TIMESTAMPDIFF(interval, datetime, datetime)

This function returns the time difference between the two times given, but only for the interval being compared. The intervals accepted are the same as those accepted for TIMESTAMPADD(). This function is available as of version 5.0.0 of MySQL. Here is an example:

SELECT NOW( ) AS Today,

TIMESTAMPDIFF(DAY, NOW( ), LAST_DAY(NOW( )))

AS 'Days Remaining in Month';

+---------------------+-------------------------+

| Today | Days Remaining in Month |

+---------------------+-------------------------+

| 2008-01-12 02:19:26 | 19 |

+---------------------+-------------------------+

This SQL statement retrieves the current date and time and uses the LAST_DAY() function to determine the date of the last day of the month. Then the TIMESTAMPDIFF() function determines the difference between the day of the date now and the day of the date at the end of the month.

Name

TO_DAYS()

Synopsis

TO_DAYS(date)

This function returns the date based on the number of days given, which are from the beginning of the currently used standard calendar. Problems occur for dates before 1582, when the Gregorian calendar became the standard. The opposite of this function is FROM_DAYS(). Here is an example:

SELECT CURDATE( ) AS 'Today',

TO_DAYS('2008-12-31'),

TO_DAYS(CURDATE( )),

(TO_DAYS('2008-12-31') -

TO_DAYS(CURDATE( )))

AS 'Days to End of Year' \G

*************************** 1. row ***************************

Today: 2008-11-03

TO_DAYS('2007-12-31'): 733772

TO_DAYS(CURDATE( )): 733714

Days to End of Year: 58

In this example, the TO_DAYS() function is used to calculate the difference in the number of days between the two dates, the number of days from the current date until the year’s end. I’ve used the \G ending instead of the semicolon so as to save space horizontally.

Name

UNIX_TIMESTAMP()

Synopsis

UNIX_TIMESTAMP([datetime])

This function returns the number of seconds since the start of the Unix epoch (January 1, 1970, Greenwich Mean Time). Without a given time, this function will return the Unix time for the current date and time. Optionally, a date and time value (directly or by way of a column value) may be given for conversion to Unix time with this function. Here is an example:

SELECT UNIX_TIMESTAMP( ) AS 'Now',

UNIX_TIMESTAMP('2008-05-09 20:45:00') AS 'Same Time from String';

+------------+-----------------------+

| Now | Same Time from String |

+------------+-----------------------+

| 1210383900 | 1210383900 |

+------------+-----------------------+

The first column uses the function to determine the Unix time for the moment that the statement was entered. The second column uses the same function to determine the Unix time for the same date and time provided in a common, readable format.

Name

UTC_DATE()

Synopsis

UTC_DATE()

This function returns the current Universal Time, Coordinated (UTC) date in yyyy-mm-dd format, or in yyyymmdd format if it’s used as part of a numeric calculation. It’s available as of version 4.1.1 of MySQL. The parentheses are optional. Here is an example:

SELECT UTC_DATE( ),

UTC_DATE( ) + 0 AS 'UTC_DATE( ) Numeric';

+-------------+---------------------+

| UTC_DATE( ) | UTC_DATE( ) Numeric |

+-------------+---------------------+

| 2008-12-07 | 20081207 |

+-------------+---------------------+

Name

UTC_TIME()

Synopsis

UTC_TIME()

This function returns the current UTC time in hh:mm:ss format, or in hhmmss format if it’s used as part of a numeric calculation. As a numeric, the microseconds are included in the results. It’s available as of version 4.1.1 of MySQL. The pair of parentheses is optional. Here is an example:

SELECT UTC_TIME( ),

UTC_TIME( ) + 0 AS 'UTC_TIME( ) Numeric';

+-------------+-------------------- +

| UTC_TIME( ) | UTC_TIME( ) Numeric |

+-------------+---------------------+

| 22:01:14 | 220114.000000 |

+-------------+---------------------+

Name

UTC_TIMESTAMP()

Synopsis

UTC_TIMESTAMP()

This function returns the current UTC date and time in yyyy-mm-dd hh:mm:ss format. It will return the UTC date and time in a yyyymmddhhmmss format if it’s used as part of a numeric calculation. As a numeric, the microseconds are included in the results. This statement is available as of version 4.1.1 of MySQL. The parentheses are optional. Use UTC_TIME() is you want only the UTC time, and UTC_DATE() if you want only the UTC date. Here is an example:

SELECT UTC_TIMESTAMP( ),

UTC_TIMESTAMP( ) + 0 AS 'UTC_TIMESTAMP( ) Numeric';

+---------------------+--------------------------+

| UTC_TIMESTAMP( ) | UTC_TIMESTAMP( ) Numeric |

+---------------------+--------------------------+

| 2008-12-07 22:08:24 | 20081207220824.000000 |

+---------------------+--------------------------+

Name

WEEK()

Synopsis

WEEK(date[, value])

This function returns the number of the week starting from the beginning of the year for the date provided. This may seem simple enough. However, it’s complex because there are one or two more days in a year beyond 52 weeks (i.e., 52×7 = 364); the first day of the year usually isn’t the first day of a week. When a year starts on a Sunday—if you consider Sunday to be the first day of the week—January 1 is definitely the first week of the year. In that case, the function should return 0 or 1 depending on whether you think of 0 as the first number or 1. If you consider Monday the first day of the week, though, then if January 1 is a Sunday, the question is whether you want that day to be considered as part of the last week of the previous year, or just as week 0 of this year and make 1 represent the first full week of the current year. All of these possibilities for MySQL to consider when executing WEEK() are represented by the mode you specify as its second parameter.

The range of values accepted for the function’s second parameter is 0 to 7. Even numbers indicate that Sunday is the first day of the week; odd values indicate Monday is the first day of the week. Codes 0, 1, 4, and 5 return results ranging from 0 to 53; codes 2, 3, 6, and 7 return results ranging from 1 to 53. Codes 0, 2, 5, and 7 determine results of the date given with regard to the year that holds the first day of the week of the week that the first day of the year given is in. Here is an example:

SELECT DAYNAME('2006-01-01') AS 'Day',

WEEK('2006-01-01', 0) AS '0(S,0)', WEEK('2006-01-01', 1) AS '1(M,0)',

WEEK('2006-01-01', 2) AS '2(S,1)', WEEK('2006-01-01', 3) AS '3(M,1)',

WEEK('2006-01-01', 4) AS '4(S,0)', WEEK('2006-01-01', 5) AS '5(M,0)',

WEEK('2006-01-01', 6) AS '6(S,1)', WEEK('2006-01-01', 7) AS '7(M,1)'

UNION ...

+-----------+--------+--------+--------+--------+--------+--------+--------+--------+

| Day | 0(S,0) | 1(M,0) | 2(S,1) | 3(M,1) | 4(S,0) | 5(M,0) | 6(S,1) | 7(M,1) |

+-----------+--------+--------+--------+--------+--------+--------+--------+--------+

| Sunday | 1 | 0 | 1 | 52 | 1 | 0 | 1 | 52 |

| Monday | 0 | 1 | 53 | 1 | 1 | 1 | 1 | 1 |

| Tuesday | 0 | 1 | 52 | 1 | 1 | 0 | 1 | 53 |

| Wednesday | 0 | 1 | 52 | 1 | 1 | 0 | 1 | 52 |

| Thursday | 0 | 1 | 52 | 1 | 0 | 0 | 53 | 52 |

| Friday | 0 | 0 | 52 | 53 | 0 | 0 | 52 | 52 |

| Saturday | 0 | 0 | 52 | 52 | 0 | 0 | 52 | 52 |

+-----------+--------+--------+--------+--------+--------+--------+--------+--------+

This results set is created with the SELECT statement shown repeated six times, joined together using UNION to merge the results into one results table. The year is adjusted for each SELECT statement, ranging from 2006 to 2011, and 2014 used in the middle for the Wednesday due to leap year. This chart shows the results of WEEK() for seven different dates (one for each day of the week), all the first day of their respective years. For each date, each row shows the results for each parameter possibility for the WEEK() function. The column headings specify the parameter used, along with whether the parameter considers Sunday or Monday (indicated by S or M, respectively) to be the first day of the week. The 0 just after the S or M indicates that results can range from 0 to 53 weeks; 1 indicates a range of 1 to 53. It’s a complex chart, but the subject is complex and it’s hoped that seeing all of the possibilities will make it easier to understand. Table 12-4 may also be useful in choosing the mode that you want.

If no mode is specified with the WEEK() function, the default is used. The default value is stored in the system variable default_week_format. It can be changed with the SET statement:

SHOW VARIABLES LIKE 'default_week_format';

+---------------------+-------+

| Variable_name | Value |

+---------------------+-------+

| default_week_format | 0 |

+---------------------+-------+

SET default_week_format = 1;

As an alternative to WEEK(), you can use YEARWEEK(). It’s synonymous with WEEK(), but with the mode of 3 only.

Table 12-4. WEEK() modes

Mode

Beginning of week

Range of weeks

Determining if week 1 is first week

0

Sunday

0–53

First day of week considered

1

Monday

0–53

2

Sunday

1–53

First day of week considered

3

Monday

1–53

4

Sunday

0–53

5

Monday

0–53

First day of week considered

6

Sunday

1–53

7

Monday

1–53

First day of week considered

Name

WEEKDAY()

Synopsis

WEEKDAY(date)

This function returns the number for the day of the week. Monday is considered the first day of the week for this function and returns a value of 0; a Sunday returns 6. Here is an example:

SELECT appt_id, client_id

FROM appointments

WHERE WEEKDAY(appt) > 4 AND

EXTRACT(YEAR_MONTH FROM appt) = EXTRACT(YEAR_MONTH FROM NOW( ));

This SQL statement, based on the WHERE clause, retrieves a list of appointments that are on the weekends of the current month.

Name

WEEKOFYEAR()

Synopsis

WEEKOFYEAR(date)

This function returns the calendar week of the year for a given date. It was added in version 4.1.1 of MySQL and is synonymous with WEEK(), but with the mode of 3 for that function only. There’s no way to change the mode for this function. Here is an example:

SELECT CURDATE( ) AS Date,

WEEKOFYEAR(CURDATE( )) AS Week;

+------------+------+

| Date | Week |

+------------+------+

| 2005-01-11 | 2 |

+------------+------+

Name

YEAR()

Synopsis

YEAR(date)

This function returns the year of the date provided. It returns values from 1,000 to 9,999, and returns 0 for a zero date. Here is an example:

SELECT YEAR('2008-01-01')

AS 'Year';

+------+

| Year |

+------+

| 2008 |

+------+

Name

YEARWEEK()

Synopsis

YEARWEEK(date[, value])

This function returns the year coupled with the number of the week into the year: yyyyww. By default, the first day of the week is Sunday and is the basis of the calculation. Optionally, you can set Monday as the first day of the week by entering a value of 1 for the second argument. This function is somewhat synonymous with WEEK(), but with the year appended to the results and the mode of 2 for that function. If you set the second parameter of this function to 1, it becomes similar to WEEK() with the mode of 3. Here is an example:

SELECT YEARWEEK('2008-01-07')

AS 'YearWeek';

+----------+

| YearWeek |

+----------+

| 200801 |

+----------+

This function can be useful in conjunction with the PERIOD_ADD() and PERIOD_DIFF() functions.