By Trudy Pelzer

Many changes have been made to temporal — DATE, TIME, DATETIME, and TIMESTAMP — functions in MySQL 4.1. This includes support for microseconds and time zones.

In this article, a time value is one of either: TIME, DATETIME, or TIMESTAMP. A date value is one of either: DATE, DATETIME, or TIMESTAMP. A datetime value is one of any of the date or time value types.

Changed Functions

Several existing functions had their functionality extended in MySQL 4.1:

ADDDATE(temporal_expression, number_of_days)

SUBDATE(temporal_expression, number_of_days)

ADDDATE and SUBDATE accept a slightly modified syntax beginning with version 4.1.1. They still operate on a date value, but now also accept a second, numeric argument.

The second argument specifies a number of days that should be added to (or subtracted from) the date value. As always, if either argument resolves to NULL, ADDDATE and SUBDATE returns NULL.

mysql> select adddate('2004-10-01',15), subdate('2004-10-01',15);+--------------------------+--------------------------+| adddate('2004-10-01',15) | subdate('2004-10-01',15) |+--------------------------+--------------------------+| 2004-10-16               | 2004-09-16               |+--------------------------+--------------------------+1 row in set (0.20 sec)

DATE_ADD(temporal_expression, INTERVAL interval_expression interval_constant)

DATE_SUB(temporal_expression, INTERVAL interval_expression interval_constant)

EXTRACT(interval_constant FROM temporal_expression)

The DATE_ADD, DATE_SUB, and EXTRACT functions accept five new interval constants — DAY_MICROSECOND, HOUR_MICROSECOND, MINUTE_MICROSECOND, SECOND_MICROSECOND, and MICROSECOND.

mysql> SELECT DATE_ADD('1992-12-31 23:59:59.000002',INTERVAL '1.999999' SECOND_MICROSECOND);+--------------------------------------------------------------------------------+| DATE_ADD('1992-12-31 23:59:59.000002', INTERVAL '1.999999' SECOND_MICROSECOND) |+--------------------------------------------------------------------------------+| 1993-01-01 00:00:01.000001                                                     |+--------------------------------------------------------------------------------+1 row in set (0.02 sec)

DATE_FORMAT(temporal_expression, format_string)

TIME_FORMAT(time_expression, format_string)

The DATE_FORMAT and TIME_FORMAT functions got a new option in MySQL 4.1.1 too. One can now use %f in the format string, to return microseconds in the range from 000000 (6 zeros) to 999999 (6 nines).

mysql> select date_format(current_timestamp,'%f');+-------------------------------------+| date_format(current_timestamp,'%f') |+-------------------------------------+| 000000                              |+-------------------------------------+1 row in set (0.02 sec)mysql> select time_format(current_time,'%f');+--------------------------------+| time_format(current_time,'%f') |+--------------------------------+| 000000                         |+--------------------------------+1 row in set (0.02 sec)

New Functions

About twenty new temporal functions were added. Many of them are implementations of similar functions from other database systems, such as MaxDB and Oracle.

ADDTIME(temporal_expression, time_expression)

SUBTIME(temporal_expression, time_expression)

The ADDTIME and SUBTIME functions provide the ability to do time arithmetic. They accept two arguments, the first of which must resolve to a time value, and the second of which must resolve to a TIME value. ADDTIME adds the time argument to the first expression and returns the result, and as you may expect, SUBTIME subtracts the time argument from the first expression and returns the result. Both functions returns NULL if either argument resolves to NULL.

mysql> select addtime('10:15:30','02:10:00'), subtime('2004-10-15 10:15:30','02:10:00');+--------------------------------+-------------------------------------------+| addtime('10:15:30','02:10:00') | subtime('2004-10-15 10:15:30','02:10:00') |+--------------------------------+-------------------------------------------+| 12:25:30                       | 2004-10-15 08:05:30                       |+--------------------------------+-------------------------------------------+1 row in set (0.20 sec)

DATEDIFF(temporal_expression, temporal_expression)

The DATEDIFF function returns the number of days between its two arguments, which must both resolve to a date value. The function returns NULL if either argument resolves to NULL.

mysql> select datediff('2004-10-20','2004-10-10');+-------------------------------------+| datediff('2004-10-20','2004-10-10') |+-------------------------------------+|                                  10 |+-------------------------------------+1 row in set (0.02 sec)

TIMEDIFF(temporal_expression, temporal_expression)

The TIMEDIFF function is another new function that provides time arithmetic functionality. This function returns the amount of time elapsed between the two temporal arguments. Both arguments must resolve to the same type of time value. TIMEDIFF returns NULL if either argument resolves to NULL.

mysql> select timediff('10:30:15','02:10:00');+---------------------------------+| timediff('10:30:15','02:10:00') |+---------------------------------+| 08:20:15                        |+---------------------------------+1 row in set (0.02 sec)

DATE(temporal_expression)

TIME(temporal_expression)

The DATE function returns the DATE portion of a date value. It returns NULL if its argument resolves to NULL.

The TIME function complements the DATE function; it takes a time value and returns its TIME portion. TIME returns NULL if its argument resolves to NULL.

mysql> select date('2004-10-01 10:15:30'), time('2004-10-01 10:15:30');+-----------------------------+-----------------------------+| date('2004-10-01 10:15:30') | time('2004-10-01 10:15:30') |+-----------------------------+-----------------------------+| 2004-10-01                  | 10:15:30                    |+-----------------------------+-----------------------------+1 row in set (0.02 sec)

MAKEDATE(year, day_of_year)

MAKETIME(hour, minute, second)

The MAKEDATE function returns the date that results from the combination of its arguments. Both arguments must resolve to a positive integer; the first specifies a YEAR value and the second specifies a given day in that year. The function returns NULL if either argument resolves to NULL. MAKEDATE also returns NULL if the day_of_year value is less than or equal to 0 (zero).

The MAKETIME function takes three arguments — an HOUR value, a MINUTE value, and a SECOND value — and returns the time that results by combining them. Each of the arguments must resolve to an integer. The function returns NULL if any of the arguments resolve to NULL.

mysql> select makedate(2004,152), maketime(10,15,30);+--------------------+--------------------+| makedate(2004,152) | maketime(10,15,30) |+--------------------+--------------------+| 2004-05-31         | 10:15:30           |+--------------------+--------------------+1 row in set (0.02 sec)

TIMESTAMP(temporal_expression)

TIMESTAMP(date_expression, time_expression)

The TIMESTAMP function has two possible formats.

In the first format, the function takes a date value and returns it as a TIMESTAMP value.

mysql> select timestamp('2004-10-15');+-------------------------+| timestamp('2004-10-15') |+-------------------------+| 2004-10-15 00:00:00     |+-------------------------+1 row in set (0.02 sec)

In the second format, TIMESTAMP accepts two arguments: a date value and a TIME value. The TIME value is added to the first expression and TIMESTAMP returns the result as a TIMESTAMP value.

mysql> select timestamp('2004-10-15','10:15:30');+------------------------------------+| timestamp('2004-10-15','10:15:30') |+------------------------------------+| 2004-10-15 10:15:30                |+------------------------------------+1 row in set, 1 warning (0.02 sec)

In each case, the function returns NULL if an argument resolves to NULL.

And now, a short digression. The TIMESTAMP data type itself was changed slightly in MySQL 4.1. Recall that in earlier versions of MySQL, TIMESTAMP includes an optional display width parameter, that lets one specify the size of a TIMESTAMP column when it is displayed, like this.

mysql> create table xy (col1 timestamp(8));Query OK, 0 rows affected (0.41 sec)mysql> insert into xy values('2004-10-01 10:15:30');Query OK, 1 row affected (0.01 sec)

With 4.0:

mysql> select * from xy;+----------+| col1     |+----------+| 20041001 |+----------+1 row in set (0.02 sec)

Although this syntax is still accepted, the option is no longer operative beginning with MySQL 4.1. From the 4.1.0 release on, a TIMESTAMP value is always returned as a string with the format 'YYYY-MM-DD HH:MM:SS' and different timestamp lengths are not supported.

Same definition, with 4.1:

mysql> select * from xy;+---------------------+| col1                |+---------------------+| 2004-10-01 10:15:30 |+---------------------+1 row in set (0.02 sec)

DAY(date_expression)

The DAY function is a synonym for DAYOFMONTH(). It returns the day portion of its date value argument. DAY returns NULL if the argument resolves to NULL.

mysql> select day('2004-10-01');+-------------------+| day('2004-10-01') |+-------------------+|                 1 |+-------------------+1 row in set (0.02 sec)

LAST_DAY(date_expression)

The LAST_DAY function takes a date value and returns the corresponding value for the last day of the given month. LAST_DAY returns NULL if the argument resolves to an invalid value (e.g. '2004-10-32') or if the argument is NULL.

mysql> select last_day('2004-10-01');+------------------------+| last_day('2004-10-01') |+------------------------+| 2004-10-31             |+------------------------+1 row in set (0.02 sec)

MICROSECOND(time_expression)

The MICROSECOND function takes a time value as an argument, and returns the microsecond portion of that value. MICROSECOND returns NULL if the argument resolves to NULL.

mysql> select microsecond('2004-10-15 10:15:30.999999');+-------------------------------------------+| microsecond('2004-10-15 10:15:30.999999') |+-------------------------------------------+|                                    999999 |+-------------------------------------------+1 row in set (0.03 sec)

WEEKOFYEAR(temporal_expression)

The WEEKOFYEAR function calculates the calendar week of its date value argument and returns the week number. The result always falls into the range from 1 (first week of the year) to 53 (last week of the year). Note that the first week of the year is the first week that has a Thursday in that year (or put another way, the week that contains January 4th). WEEKOFYEAR returns NULL if its argument resolves to NULL.

mysql> select weekofyear('2004-10-15'), weekofyear('2000-01-01');+--------------------------+--------------------------+| weekofyear('2004-10-15') | weekofyear('2000-01-01') |+--------------------------+--------------------------+|                       42 |                       52 |+--------------------------+--------------------------+1 row in set (0.01 sec)

STR_TO_DATE(temporal_string, format_string)

The STR_TO_DATE function converts its temporal_string argument into a DATE, TIME, DATETIME, or TIMESTAMP value with the format specified by its format_string argument.

The format_string argument accepts the same formats as the DATE_FORMAT function; the temporal_string given must be a temporal value written in the same format. STR_TO_DATE returns NULL if the temporal_string is an invalid value for the given format, or if either argument resolves to NULL.

mysql> select str_to_date('2004-10-15 10:15:30','%Y-%m-%d %H:%i:%s');+--------------------------------------------------------+| str_to_date('2004-10-15 10:15:30','%Y-%m-%d %H:%i:%s') |+--------------------------------------------------------+| 2004-10-15 10:15:30                                    |+--------------------------------------------------------+1 row in set (0.02 sec)mysql> select str_to_date('2004-10-15 10:15:30','%d.%m.%y %H.%i');+-----------------------------------------------------+| str_to_date('2004-10-15 10:15:30','%d.%m.%y %H.%i') |+-----------------------------------------------------+| NULL                                                |+-----------------------------------------------------+1 row in set (0.02 sec)

GET_FORMAT(temporal_constant, format_string_constant)

The GET_FORMAT function returns a format string, like the ones we're used to using as arguments for the DATE_FORMAT and TIME_FORMAT functions. The temporal_constant argument has four possible values: DATE, TIME, DATETIME, and TIMESTAMP (but not until 4.1.4 for TIMESTAMP). The format_string_constant argument has five possible values, which must be enclosed in single quotes: 'EUR', 'USA', 'JIS', 'ISO', and 'INTERNAL'.

mysql> select get_format(timestamp,'iso');+-----------------------------+| get_format(timestamp,'iso') |+-----------------------------+| %Y-%m-%d %H:%i:%s           |+-----------------------------+1 row in set (0.02 sec)

The combination of valid values for GET_FORMAT's two arguments gives us up to 20 possible returns from the function: five for DATE, five for TIME, five for DATETIME, and five more for TIMESTAMP. When used with TIMESTAMP as the temporal constant, GET_FORMAT returns the same values as shown for DATETIME.

Function Call	Result
GET_FORMAT(DATE,'USA')	'%m.%d.%Y'
GET_FORMAT(DATE,'JIS')	'%Y-%m-%d'
GET_FORMAT(DATE,'ISO')	'%Y-%m-%d'
GET_FORMAT(DATE,'EUR')	'%d.%m.%Y'
GET_FORMAT(DATE,'INTERNAL')	'%Y%m%d'
GET_FORMAT(DATETIME,'USA')	'%Y-%m-%d-%H.%i.%s'
GET_FORMAT(DATETIME,'JIS')	'%Y-%m-%d %H:%i:%s'
GET_FORMAT(DATETIME,'ISO')	'%Y-%m-%d %H:%i:%s'
GET_FORMAT(DATETIME,'EUR')	'%Y-%m-%d-%H.%i.%s'
GET_FORMAT(DATETIME,'INTERNAL')	'%Y%m%d%H%i%s'
GET_FORMAT(TIME,'USA')	'%h:%i:%s %p'
GET_FORMAT(TIME,'JIS')	'%H:%i:%s'
GET_FORMAT(TIME,'ISO')	'%H:%i:%s'
GET_FORMAT(TIME,'EUR')	'%H.%i.%S'
GET_FORMAT(TIME,'INTERNAL')	'%H%i%s'

These formats came from how MaxDB defines its date and time formats.

The MySQL Reference Manual shows two examples of GET_FORMAT used in conjunction with the DATE_FORMAT and STR_TO_DATE functions, which I'll repeat here.

mysql> SELECT DATE_FORMAT('2003-10-03',GET_FORMAT(DATE,'EUR'));    -> '03.10.2003'mysql> SELECT STR_TO_DATE('10.31.2003',GET_FORMAT(DATE,'USA'));    -> 2003-10-31

UTC_DATE

UTC_DATE()

UTC_TIME

UTC_TIME()

UTC_TIMESTAMP

UTC_TIMESTAMP()

These functions, which can be written either with or without the parentheses, are niladic functions — they take no arguments.

UTC_DATE returns the current UTC (or Universal Coordinated Time) date as a value in either 'YYYY-MM-DD' or YYYYMMDD form (depending on whether the function is used in a string or numeric context). By the way, most people still call UTC time "Greenwich Mean Time".

The UTC_TIME function returns the current UTC time as a value in 'HH:MM:SS' or HHMMSS form (depending on whether the function is used in a string or numeric context).

The UTC_TIMESTAMP function returns the current UTC date and time as a value in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS form (depending on whether the function is used in a string or numeric context).

mysql> select utc_date(), utc_date+1;+------------+------------+| utc_date() | utc_date+1 |+------------+------------+| 2004-09-14 |   20040915 |+------------+------------+1 row in set (0.02 sec)mysql> select utc_time(), utc_time+1;+------------+------------+| utc_time() | utc_time+1 |+------------+------------+| 16:09:12   |     160913 |+------------+------------+1 row in set (0.03 sec)mysql> select utc_timestamp(), utc_timestamp+1;+---------------------+-----------------+| utc_timestamp()     | utc_timestamp+1 |+---------------------+-----------------+| 2004-09-14 16:12:36 |  20040914161237 |+---------------------+-----------------+1 row in set (0.02 sec)

Time Zone Handling

MySQL 4.1 includes increased support for manipulating temporal values based on the time zone in which they exist.

For example, beginning with MySQL 4.1.3, the CURRENT_DATE(), CURRENT_TIME(), CURRENT_TIMESTAMP(), FROM_UNIXTIME(), LOCALTIME, LOCALTIMESTAMP, NOW, SYSDATE, and UNIX_TIMESTAMP() functions return values in the connection's current time zone, while the UTC_DATE(), UTC_TIME(), and UTC_TIMESTAMP() functions return values in Universal Coordinated, or UTC, time.

In addition, the new CONVERT_TZ function provides the ability to convert a DATETIME or TIMESTAMP value from one time zone to another.

Finally, data values of the TIMESTAMP data type are automatically interpreted as values belonging to the connection's current time zone. TIMESTAMP values thus behave the way they do in Oracle's TIMESTAMP WITH LOCAL TIME ZONE data type — that is, values stored in a TIMESTAMP column are normalized towards UTC and converted back to the current connection time zone at SELECT time. This gives MySQL the ability to provide the "current" TIMESTAMP value, even if the database is moved to a different location.

This brings up another point about changes to the TIMESTAMP data type.

The internal representation of TIMESTAMP values in InnoDB tables was changed between version 4.0 and 4.1, and then back again in version 4.1.4. This change will result in incorrect values in TIMESTAMP columns belonging to InnoDB tables after an upgrade.

To correct this problem, the course of action, when upgrading from MySQL 4.1.3 or earlier to version 4.1.4, is to use mysqldump to save, and then restore, all InnoDB tables that contain TIMESTAMP columns.

Now, back to time zones. Recall that in previous versions of MySQL, the time zone that is relevant for the server can be set either with the --timezone=timezone_name option to mysqld_safe or with the TZ environment variable when starting mysqld. That changed in MySQL 4.1.3.

Here's what happens now:

Beginning with MySQL 4.1.3, the MySQL server maintains several time zone settings: the system time zone, the server's current time zone, and a connection time zone for each client.

The system time zone.

When the server starts, it looks for the time zone of the host machine and uses that value to automatically set the system_time_zone system variable.

This variable replaces the old timezone system variable, which has been removed. So users migrating from an earlier version of MySQL will need to ensure that they replace all instances of the timezone system variable with references to system_time_zone instead.

The server's current time zone.

The dynamic global time_zone system variable specifies the time zone in which the server is running. Its initial value is 'SYSTEM', which simply means that the server time zone and the system time zone are the same.

This initial value can be explicitly set with the --default-time-zone=timezone option. Users with the SUPER privilege can also set the global value at runtime, with the SET GLOBAL time_zone statement, as shown here.

mysql> SET GLOBAL time_zone = timezone;

To get the current global time zone value, SELECT the @@global.time_zone variable:

mysql> SELECT @@global.time_zone;

The connection time zone for each client.

Each client that connects to MySQL has its own time zone setting, specified by the dynamic session time_zone variable. The initial value of this variable is the same as the global time_zone variable, but can be reset at runtime with the SET time_zone statement.

mysql> SET time_zone = timezone;

To get the current session time zone value, SELECT the @@session.time_zone variable:

mysql> SELECT @@session.time_zone;

You'll notice the = timezone argument in the SET GLOBAL time_zone and SET time_zone statements, as well as the same argument in the --default-time-zone server option. The values for these arguments can be given in one of three ways:

1. As a string that specifies an offset from UTC, in the form '[+|-]HH:MM', for example '-7:00', to indicate the time zone that is seven hours earlier than UTC, Mountain Time. This method is always possible, regardless of which operating system MySQL is running under and whether or not the MySQL time zone tables have been populated.
2. As a string that provides a full time zone name, for example 'Mountain Daylight Time'.
3. Or as a string that provides an abbreviation for a full time zone name, for example, 'MDT', which is the abbreviation for 'Mountain Daylight Time'. By the way, the names I've used here are not universally accepted, since time zone names and abbreviations are operating system specific.

These last two methods work only on Unix-based systems and only if the time zone-related tables in the mysql database have been created and populated.

Now, it's important to remember that, although installing MySQL 4.1.3 or higher creates the time zone tables, the procedure does not also populate them. This last step must be done manually.

Also, users who are migrating from an earlier version of MySQL need to create the time zone tables separately, by upgrading the mysql database. If they don't, they won't be able to take advantage of the new time zone features.

So there's two things to do to ensure a smooth transition between MySQL 4.0 (or earlier) and 4.1.3 (and higher).

Step 1: Only if migrating from 4.1.2 or earlier:

Create the correct time zone tables, by running the mysql_fix_privilege_tables script.

Step 2: For all users running MySQL 4.1.3 or later on a Unix-based system (recall this doesn't work on Windows systems yet):

Populate the time zone tables.

To do so, run the mysql_tzinfo_to_sql program, provided with the MySQL distribution. mysql_tzinfo_to_sql reads the operating system time zone files and generates SQL statements from them. The SQL statements are then processed by mysql, to load the time zone tables.

To run mysql_tzinfo_to_sql successfully, one needs to know where the server machine's operating system time zone files are stored; check for a directory with a name similar to /usr/share/zoneinfo. Pass the directory name on the command line to mysql_tzinfo_to_sql, and send the output into the mysql program. Here's an example.

shell> mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql

mysql_tzinfo_to_sql can also be used to generate leap second information. An installation that needs to account for leap seconds within the database can initialize the leap second information by passing the name of a file holding the required information to the mysql program, as shown here.

shell> mysql_tzinfo_to_sql --leap file-name | mysql -u root mysql

Finally, there's one new function that makes use of the time zone tables to convert a datetime value between two timezones:

CONVERT_TZ(temporal_expression, from_timezone, to_timezone)

The CONVERT_TZ function takes three arguments. The first argument must resolve to a DATETIME or TIMESTAMP value, while the others must each resolve to a time zone in the form '[+|-]HH:MM' or a named time zone identifier, such as 'GMT' or (on some systems) 'Greenwich Mean Time'. Named time zones can only be used if the time zone tables are properly set up.

CONVERT_TZ assumes that the first argument is a datetime value that belongs to the time zone given in the from_timezone argument. The function converts that value into a datetime value belonging to the time zone given in the to_timezone argument.

In effect, you get the same datetime value, but from the point of view of a different time zone. The function returns NULL if any of the arguments are invalid, and also if any of the arguments resolve to NULL.

mysql> select convert_tz('2004-10-01 10:15:30','+01:00','+07:00');+-----------------------------------------------------+| convert_tz('2004-10-01 10:15:30','+01:00','+07:00') |+-----------------------------------------------------+| 2004-10-01 16:15:30                                 |+-----------------------------------------------------+1 row in set (0.02 sec)

Fractional Seconds

Fractional seconds support is simple. We have very little of it with 4.1, but the foundation has been laid for adding more support in later versions.

At this time, MySQL does not provide the ability to define a fractional-seconds precision for a time value column, although it is planned to add this ability to the TIMESTAMP data type in a future version. In fact, the syntax is accepted, but nothing is done with the fractional-seconds definition.

The reason the syntax is accepted, of course, is due to the fact that in previous versions of MySQL, one could define a display width for the TIMESTAMP data type, as I alluded to earlier.

It is possible to INSERT a TIMESTAMP value that includes a fractional-seconds portion into a TIMESTAMP column without getting an error. But it isn't possible to get the complete value, or even just the microseconds portion, back out because the microseconds are just chopped off at INSERT time.

mysql> create table xy(tscol timestamp);Query OK, 0 rows affected (0.09 sec)mysql> insert into xy (tscol) values ('2004-10-15 10:15:30.999999');Query OK, 1 row affected (0.01 sec)mysql> select * from xy;+---------------------+| tscol               |+---------------------+| 2004-10-15 10:15:30 |+---------------------+1 row in set (0.04 sec)mysql> select extract(microsecond from tscol) from xy;+---------------------------------+| extract(microsecond from tscol) |+---------------------------------+|                               0 |+---------------------------------+1 row in set (0.00 sec)

Nor do the niladic temporal functions that could include fractional seconds — CURRENT_TIME, CURRENT_TIMESTAMP, LOCALTIME, LOCALTIMESTAMP, NOW, SYSDATE, and so on — return values that include a fractional-seconds portion.

So, what do we do with microseconds?

Well, MySQL 4.1 can do temporal arithmetic that takes microseconds into account. As I said earlier, both the DATE_ADD and the DATE_SUB functions will now accept an INTERVAL qualifier of either MICROSECOND, SECOND_MICROSECOND, MINUTE_MICROSECOND, HOUR_MICROSECOND, or DAY_MICROSECOND — and will add (or subtract, respectively) the given value to or from a given temporal value. The same is true for temporal arithmetic done with the INTERVAL keyword.

mysql> select current_timestamp + INTERVAL 1.999999 second_microsecond;+----------------------------------------------------------+| current_timestamp + INTERVAL 1.999999 second_microsecond |+----------------------------------------------------------+| 2004-09-16 16:28:45.999999                               |+----------------------------------------------------------+1 row in set (0.02 sec)

The new ADDTIME and SUBTIME functions also do time arithmetic that includes microseconds. So does the TIMEDIFF function. The DATE_FORMAT, TIME_FORMAT and STR_TO_DATE functions now accept format strings with the ability to properly display temporal values that include a fractional-seconds portion. The EXTRACT and MICROSECOND functions can now return the fractional-seconds portion of a temporal value. And the TIME and TIMESTAMP functions can return values that include a fractional-seconds portion.

To summarize: MySQL's fractional-seconds support beginning with version 4.1 allows one to manipulate temporal values that include microseconds, but does not allow one to actually store such values in the database.