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
andSUBDATE
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
andSUBDATE
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
, andEXTRACT
functions accept five new interval constants —DAY_MICROSECOND
,HOUR_MICROSECOND
,MINUTE_MICROSECOND
,SECOND_MICROSECOND
, andMICROSECOND
.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
andTIME_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
andSUBTIME
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 aTIME
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 theDATE
portion of a date value. It returns NULL if its argument resolves to NULL.The
TIME
function complements theDATE
function; it takes a time value and returns itsTIME
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 aYEAR
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 — anHOUR
value, aMINUTE
value, and aSECOND
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 aTIME
value. TheTIME
value is added to the first expression andTIMESTAMP
returns the result as aTIMESTAMP
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 forDAYOFMONTH
(). 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 aDATE
,TIME
,DATETIME
, orTIMESTAMP
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 theDATE_FORMAT
andTIME_FORMAT
functions. The temporal_constant argument has four possible values:DATE
,TIME
,DATETIME
, andTIMESTAMP
(but not until 4.1.4 forTIMESTAMP
). 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 forDATE
, five forTIME
, five forDATETIME
, and five more forTIMESTAMP
. When used withTIMESTAMP
as the temporal constant,GET_FORMAT
returns the same values as shown forDATETIME
.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 theDATE_FORMAT
andSTR_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 thetimezone
system variable with references tosystem_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 theSUPER
privilege can also set the global value at runtime, with theSET 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 globaltime_zone
variable, but can be reset at runtime with theSET 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:
- 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.
- As a string that provides a full time zone name, for example 'Mountain Daylight Time'.
- 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 bymysql
, 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 tomysql_tzinfo_to_sql
, and send the output into themysql
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 aDATETIME
orTIMESTAMP
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.
0