Using MySQL Date and Time Functions, Part 3

Using MySQL Date and Time Functions, Part 3

Here is Part 3 of this tutorial for MySQL Date and Time Functions. Here are the links to Part 1 and 2:

Here is the list of MySQL Date and Time Functions for Part 3:

No.

Name

Description

MICROSECOND(expr)

Returns the microseconds from the time or datetime expression expr as a number in the range from 0 to 999999. See Practice #29.

MINUTE(time)

Returns the minute for time, in the range 0 to 59. See Practice #30.

MONTH(date)

Returns the month for date, in the range 1 to 12 for January to December, or 0 for dates such as '0000-00-00' or '2008-00-00' that have a zero month part. See Practice #31.

MONTHNAME(date)

Returns the full name of the month for date. See Practice #32.

NOW()

Returns the current date and time as a value in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS.uuuuuu format, depending on whether the function is used in a string or numeric context. The value is expressed in the current time zone. See Practice #33-1.

Difference between NOW and SYSDATE:

NOW() returns a constant time that indicates the time at which the statement began to execute. That means, if you use two NOW() functions within the same statement, the second NOW() returns the time the statement began to execute, not the time when the second NOW() function is called.

If you use NOW() within a stored routine or trigger, it returns the time at which the routine or triggering statement began to execute.

See Practice #33-2.
SYSDATE() returns Returns the current date and time at which it executes. If you use two SYSDATE() within the same statement, the two SYSDATE() may return different values. This is because SYSDATE returns the time when it actually executes.

See Practice #33-3.

PERIOD_ADD(P,N)

Add a period to a date in year-month format.

It adds N months to period P (in the format YYMM or YYYYMM). Returns a value in the format YYYYMM. Note that the period argument P is not a date value. See Practice #34.

PERIOD_DIFF(P1,P2)

Returns the number of months between two periods P1 and P2. P1 and P2 should be in the format YYMM or YYYYMM. See Practice #35.

QUARTER(date)

Returns the quarter for date, in the range 1 to 4. See Practice #36.

SEC_TO_TIME(seconds)

Returns the seconds argument, converted to hours, minutes, and seconds, as a TIME value in either HH:MM:SS or HHMMSS.000000 format, depending on if the function is used in string or numeric context. The range of the result is constrained to that of the TIME data type. See Practice #37.

SECOND(time)

Returns the second for time, in the range 0 to 59. See Practice #38.

STR_TO_DATE(str,format)

This is the inverse of the DATE_FORMAT() function. It takes a string str and a format string format. STR_TO_DATE() returns a DATETIME value if the format string contains both date and time parts, or a DATE or TIME value if the string contains only date or time parts.

The date, time, or datetime values contained in str should be given in the format indicated by format. For the specifiers that can be used in format, see the DATE_FORMAT() function description. If str contains an illegal date, time, or datetime value, STR_TO_DATE() returns NULL. An illegal value also produces a warning.

See Practice #39.

SUBDATE(date,INTERVAL expr unit)
SUBDATE(expr,days)

When invoked with the INTERVAL form of the second argument, SUBDATE() is a synonym for DATE_SUB(). For information on the INTERVAL unit argument, see the discussion for DATE_ADD(). See Practice #40-1.

The second form allows the use of an integer value for days. In such cases, it is interpreted as the number of days to be subtracted from the date or datetime expression expr. See Practice #40-2.

SUBTIME(expr1,expr2)

SUBTIME function returns expr1 – expr2 expressed as a value in the same format as expr1. expr1 is a time or datetime expression, and expr2 is a time expression. See Practice #41.

SYSDATE()

Returns the current date and time as a value in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS.uuuuuu format, depending on whether the function is used in a string or numeric context.

SYSDATE() returns the time at which it executes. This differs from the behavior for NOW(), which returns a constant time that indicates the time at which the statement began to execute. (Within a stored routine or trigger, NOW() returns the time at which the routine or triggering statement began to execute.)

See Practice #33-2 and Practice #33-3 to compare difference between SYSDATE and NOW.

TIME_FORMAT(time,format)

This is used like the DATE_FORMAT() function, but the format string may contain format specifiers only for hours, minutes, and seconds. Other specifiers produce a NULL value or 0.

If the time value contains an hour part that is greater than 23, the %H and %k hour format specifiers produce a value larger than the usual range of 0..23. The other hour format specifiers produce the hour value modulo 12.

See Practice #43

TIME_TO_SEC(time)

Converts the time argument to the number of seconds. See Practice #44.

TIME(expr)

Extracts the time portion of the time or datetime expression expr and returns it as a string. See Practice #45.

TIMEDIFF(expr1,expr2)

TIMEDIFF returns expr1 – expr2 expressed as a time value. expr1 and expr2 are time or date-and-time expressions, but both must be of the same type. See Practice #46.

TIMESTAMP (expr)
TIMESTAMP(expr1,expr2)

With one argument, TIMESTAMP(expr) returns the date or datetime expression expr as a datetime value. See Practice #47-1.

With two arguments, TIMESTAMP(expr1,expr2) adds the time expression expr2 to the date or datetime expression expr1 and returns the result as a datetime value. See Practice #47-2.

TIMESTAMPADD(
unit,interval,datetime_expr)

Adds the integer expression interval to the date or datetime expression datetime_expr.

The unit for interval is given by the unit argument, which should be one of the following values:

FRAC_SECOND (microseconds)
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR

See Practice #48.

TIMESTAMPDIFF(
unit,datetime_expr1,datetime_expr2)

Returns the integer difference between the date or datetime expressions datetime_expr1 and datetime_expr2. The unit for the result is given by the unit argument.

The legal values for unit are the same as those listed in the description of the TIMESTAMPADD() function. See Practice #49.

TO_DAYS(date)

Given a date, returns a day number (the number of days since year 0).

The date argument can be in either YYYY-MM-DD format, YYYYMMDD format, or YYMMDD format. See Practice #50.

UNIX_TIMESTAMP()
UNIX_TIMESTAMP(date)

Return a UNIX timestamp.

If called with no argument, returns a Unix timestamp (seconds since '1970-01-01 00:00:00' UTC) as an unsigned integer. See Practice #51-1.

If UNIX_TIMESTAMP() is called with a date argument, it returns the value of the argument as seconds since '1970-01-01 00:00:00' UTC. date may be a DATE string, a DATETIME string, a TIMESTAMP, or a number in the format YYMMDD or YYYYMMDD. See Practice #51-2.

When called with a date argument, the server interprets argument date as a value in the current time zone of the server and converts it to an internal value in UTC. So the UTC value returned from UNIX_TIMESTAMP(date) is relative to the MySQL server's current time zone setup.

UTC_DATE
UTC_DATE()

Returns the current UTC date as a value in 'YYYY-MM-DD' or YYYYMMDD format, depending on whether the function is used in a string or numeric context. See Practice #52.

UTC_TIME

Returns the current UTC time as a value in 'HH:MM:SS' or HHMMSS.uuuuuu format, depending on whether the function is used in a string or numeric context. See Practice #53.

UTC_TIMESTAMP
UTC_TIMESTAMP()

Returns the current UTC date and time as a value in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS.uuuuuu format, depending on whether the function is used in a string or numeric context. See Practice #54.

WEEK(date,[mode])

This function returns the week number for date.

The two-argument form of WEEK() allows you to specify whether the week starts on Sunday or Monday and whether the return value should be in the range from 0 to 53 or from 1 to 53. See Practice #55.

If the mode argument is omitted, the value of the default_week_format system variable is used.

The following table describes how the mode argument works.

Mode	First day of week	Return Value Range	Week 1 is the first week …
0	Sunday	0-53	with a Sunday in this year. This is the default value but can be changed at server startup using options on the command line or in an option file, or changed dynamically while the server is running by means of the SET statement.
1	Monday	0-53	with more than 3 days this year
2	Sunday	1-53	with a Sunday in this year
3	Monday	1-53	with more than 3 days this year
4	Sunday	0-53	with more than 3 days this year
5	Monday	0-53	with a Monday in this year
6	Sunday	1-53	with more than 3 days this year
7	Monday	1-53	with a Monday in this year

WEEKDAY(date)

Returns the weekday index for date (0 = Monday, 1 = Tuesday, 2 = Wednesday, 3 = Thursday, 4 = Friday, 5 = Saturday, 6 = Sunday). See Practice #56.

WEEKOFYEAR(date)

Returns the ISO calendar week number of the date. Calendar week number is a number in the range from 1 to 53 and the year starts with a Monday.

WEEKOFYEAR() is a compatibility function that is equivalent to
WEEK(date,3). See Practice #57.

YEAR(date)

Returns the year for date, in the range 1000 to 9999, or 0 for the "zero" date.

date can be in four different formats:

YYYY-MM-DD
YY-MM-DD
YYYYMMDD
YYMMDD

See Practice #58.

YEARWEEK(date)
YEARWEEK(date,[mode])

Returns year and week for a date. The mode argument works exactly like the mode argument to WEEK(). The year in the result may be different from the year in the date argument for the first and the last week of the year.

Note that the week number is different from what the WEEK() function would return (0) for optional arguments 0 or 1, as WEEK() then returns the week in the context of the given year. See Practice #59.

Practice #29: Using MICROSECOND function.

In Firefox (not IE), copy and paste the following SQL to your SQLyog query window. Note that the SQL needs to end with semi-colon if you have multiple queries in the query window. Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.

-- Extract the microseconds from the date and time value given
SELECT MICROSECOND('12:00:00.123456') AS value1, 
    MICROSECOND('1997-12-31 23:59:59.000010') AS value2;

Query result set - 1 row returned:
Using MICROSECOND function

Practice #30: Using MINUTE function.

-- Get minute of the given time
SELECT MINUTE('98-02-03 10:05:03');

Query result set - 1 row returned:
Using MINUTE function

Practice #31: Using MONTH function.

-- Get month for date
SELECT MONTH('1998-02-03'), MONTH('2008-00-00');

Query result set - 1 row returned:
Using MONTH function

Practice #32: Using MONTHNAME function.

-- Get month name
SELECT MONTHNAME('1998-02-05');

Query result set - 1 row returned:
Using MONTHNAME function

Practice #33-1: Using NOW() function.

/*
Return current time in 'YYYY-MM-DD HH:MM:SS' 
or YYYYMMDDHHMMSS.uuuuuu format
*/
SELECT NOW(), NOW() + 0;

Query result set - 1 row returned:
Using NOW function

Practice #33-2: Using two NOW() functions within the same statement.

/*
Two NOW() functions are used in the same statement.
They return the same value because NOW() returns the
time when the statement began to execute, rather than
the time when NOW() is called.
 
SLEEP is used to force the statement to wait for 2 seconds
before calling the second NOW() function.
 
The two NOW() functions returned the same value.
*/
SELECT NOW(), SLEEP(2), NOW();

Query result set - 1 row returned:
Using two NOW() functions within the same statement

Practice #33-3: Using NOW() function and SYSDATE() within the same statement.

/*
NOW() and SYSDATE() function are used in the same statement.
NOW() returns the time when the statement began to execute.
SYSDATE() returns the time when it is called.
 
SLEEP is used to force the statement to wait for 2 seconds
before calling SYSDATE() function.
 
NOW() and SYSDATE() returned different values.
*/
SELECT NOW(), SLEEP(2), SYSDATE();

Query result set - 1 row returned:
Using NOW() function and SYSDATE() within the same statement

Practice #34: Using PERIOD_ADD function.

/*
Add 2 months to a year-month in the format of
YYMM (9801) or YYYYMM (199801)
*/
SELECT PERIOD_ADD(9801,2), PERIOD_ADD(199801,2);

Query result set - 1 row returned:
Using PERIOD_ADD function

Practice #35: Using PERIOD_DIFF function.

/*
Calculate the number of months between the first period
and the second period. Period is in the format of 
YYMM (9802) or YYYYMM (199703)
*/
SELECT PERIOD_DIFF(9802,199703);

Query result set - 1 row returned:
Using PERIOD_DIFF function

Practice #36: Using QUARTER function.

-- Get the quarter for date 98-04-09.
SELECT QUARTER('98-04-09');

Query result set - 1 row returned:
Using QUARTER function

Practice #37: Using SEC_TO_TIME function.

/*
If not used in arithmetic operation, SEC_TO_TIME returns 
a time in HH:MM:SS format by converting the number of 
seconds given to hour, minute, and second.
 
If used in arithmetic operation, SEC_TO_TIME returns 
a time in HHMMSS.000000 format by converting the number 
of seconds given to hour, minute, and second.
*/
SELECT SEC_TO_TIME(63239), SEC_TO_TIME(63239)+1;

Query result set - 1 row returned:
Using SEC_TO_TIME function

Practice #38: Using SECOND function.

-- Return second part for the given time
SELECT SECOND('10:05:03');

Query result set - 1 row returned:
Using SECOND function

Practice #39: Using STR_TO_DATE function.

/*
Convert a date string from a format indicated by the 
given format to a DATETIME value. 
*/
SELECT STR_TO_DATE('04/31/2004', '%m/%d/%Y');

Query result set - 1 row returned:
Using STR_TO_DATE function

Practice #40-1: Using SUBDATE function.

/*
When used with three arguments, SUBDATE function
can be used to subtract any number of units - year,
month, day, hour, minute, second, or microsecond.
*/
SELECT SUBDATE('1998-01-02', INTERVAL 31 MONTH);

Query result set - 1 row returned:
Using SUBDATE function

Practice #40-2: Using SUBDATE function.

/*
When used only with two arguments, the second
argument is always number of days.
*/
SELECT SUBDATE('1998-01-02 12:00:00', 31);

Query result set - 1 row returned:
Using SUBDATE function

Practice #41: Using SUBTIME function.

/*
Subtract times and return a value in the same format as 
the first argument.
*/
SELECT SUBTIME('1997-12-31 23:59:59.999999','1 1:1:1.000002') AS time1,
    SUBTIME('01:00:00.999999', '02:00:00.999998') AS time2;

Query result set - 1 row returned:
Using SUBTIME function

Practice #43: Using TIME_FORMAT function.

-- Format a time
SELECT TIME_FORMAT('100:00:00', '%H %k %h %I %l');

Query result set - 1 row returned:
Using TIME_FORMAT function

Practice #44: Using TIME_TO_SEC function.

-- Convert the time value in the argument 
-- to the number of seconds.
SELECT TIME_TO_SEC('22:23:00');

Query result set - 1 row returned:
Using TIME_TO_SEC function

Practice #45: Using TIME function.

-- Extract time portion of the date or 
-- datetime argument passed in
SELECT TIME('2003-12-31 01:02:03') AS time1,
    TIME('2003-12-31 01:02:03.000123') AS time1;

Query result set - 1 row returned:
Using TIME function

Practice #46: Using TIMEDIFF function.

-- Calculate time difference
SELECT TIMEDIFF('1997-12-31 23:59:59.000001',
    '1997-12-30 01:01:01.000002') AS Diff;

Query result set - 1 row returned:
Using TIMEDIFF function

Practice #47-1: Using TIMESTAMP function with one argument.

-- Returns a datetime value
SELECT TIMESTAMP('2003-12-31');

Query result set - 1 row returned:
Using TIMESTAMP function with one argument

Practice #47-2: Using TIMESTAMP function with two arguments.

-- Adds a time value to a datetime value
SELECT TIMESTAMP('2003-12-31 12:00:00','12:00:00');

Query result set - 1 row returned:
Using TIMESTAMP function with two arguments

Practice #48: Using TIMESTAMPADD function with two arguments.

-- Adds one week to datetime value
SELECT TIMESTAMPADD(WEEK,1,'2003-01-02 12:00:00');

Query result set - 1 row returned:
Using TIMESTAMPADD function with two arguments

Practice #49: Using TIMESTAMPDIFF function.

/*
Subtract the month part in the second argument
from the month part in the first argument.
*/
SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01');

Query result set - 1 row returned:
Using TIMESTAMPDIFF function

Practice #50: Using TO_DAYS function.

/*
Returns a day number (the number of days since year 0). 
 
The argument is a date in either YYYY-MM-DD format,
YYYYMMDD format, or YYMMDD format. 
*/
SELECT TO_DAYS('1997-10-07'), TO_DAYS('19971007'), TO_DAYS('971007');

Query result set - 1 row returned:
Using TO_DAYS function

Practice #51-1: Using UNIX_TIMESTAMP function.

-- Get current Unix timestamp value
SELECT UNIX_TIMESTAMP();

Query result set - 1 row returned:
Using UNIX_TIMESTAMP function

Practice #51-2: Using UNIX_TIMESTAMP function with an argument.

/*
UNIX_TIMESTAMP can be called with an argument. The 
argument can be a DATE string, a DATETIME string, 
a TIMESTAMP, or a number in the format YYMMDD or YYYYMMDD.
*/
SELECT UNIX_TIMESTAMP('2007-11-30 10:30:19') AS datetime_format,
    UNIX_TIMESTAMP('2007-11-30') AS date_format,
    UNIX_TIMESTAMP('20071130') AS YYYYMMDD_format,
    UNIX_TIMESTAMP('071130') AS YYMMDD_format;

Query result set - 1 row returned:
Using UNIX_TIMESTAMP function with an argument

Practice #52: Using UTC_DATE function.

/*
Returns the current UTC date as a value in 'YYYY-MM-DD' format
if the function is used in a string context, or YYYYMMDD format
if used in numeric context such as arithmetic operation.
*/
SELECT UTC_DATE() AS "YYYY-MM-DD format", 
    UTC_DATE() + 0 AS "YYYYMMDD format";

Query result set - 1 row returned:
Using UTC_DATE function

Practice #53: Using UTC_TIME function.

/*
Returns the current UTC time as a value in 
'HH:MM:SS' format if the function is used 
in a string context, or HHMMSS.uuuuuu format
if used in numeric context such as arithmetic operation.
*/
SELECT UTC_TIME() AS "HH:MM:SS format", 
    UTC_TIME() + 0 AS "HHMMSS.uuuuuu format";

Query result set - 1 row returned:
Using UTC_TIME function

Practice #54: Using UTC_TIMESTAMP function.

/*
Returns the current UTC date and time as a value in 
'YYYY-MM-DD HH:MM:SS' format if the function is used 
in a string context, or YYYYMMDDHHMMSS.uuuuuu format
if used in numeric context such as arithmetic operation.
*/
SELECT UTC_TIMESTAMP() AS "YYYY-MM-DD HH:MM:SS format", 
    UTC_TIMESTAMP() + 0 AS "YYYYMMDDHHMMSS.uuuuuu format";

Query result set - 1 row returned:
Using UTC_TIMESTAMP function

Practice #55: Using WEEK function.

-- Returns the week number.
SELECT WEEK('1998-02-20'), WEEK('1998-02-20',0), WEEK('1998-02-20',1);

Query result set - 1 row returned:
Using WEEK function

Practice #56: Using WEEKDAY function.

/*
Returns weekday index. 0 = Monday, 1 = Tuesday, 2 = Wednesday, 
3 = Thursday, 4 = Friday, 5 = Saturday, 6 = Sunday
*/
SELECT WEEKDAY('1998-02-03 22:23:00'), WEEKDAY('1997-12-22');

Query result set - 1 row returned:
Using WEEKDAY function

Practice #57: Using WEEKOFYEAR function.

/*
WEEKOFYEAR returns ISO calendar week number and is 
equivalent to WEEK function with a mode argument of 3.
*/
SELECT WEEKOFYEAR('2008-06-07'), WEEK('2008-06-07',3);

Query result set - 1 row returned:
Using WEEKOFYEAR function

Practice #58: Using YEAR function.

-- Returns year of a date in four different formats
SELECT YEAR('1998-02-03') AS "YYYY-MM-DD format", 
    YEAR('98-02-03') AS "YY-MM-DD format", 
    YEAR('19980203') AS "YYYYMMDD format", 
    YEAR('980203') AS "YYMMDD format";

Query result set - 1 row returned:
Using YEAR function

Practice #59: Using YEARWEEK function.

-- Returns year and week for a date.
SELECT YEARWEEK('1987-01-01');

Query result set - 1 row returned:
Using YEARWEEK function

Next tutorial: Using Conversion Functions
Back to Section Index: Single Row Functions
Back to Tutorial Index: MySQL Database How-to Articles and Tutorials

	Custom Search