Using MySQL Date and Time Functions, Part 2

Using MySQL Date and Time Functions, Part 2

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

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

No.

Name

Description

DATE_FORMAT(date,format)

Formats the date value according to the format string.

The following specifiers may be used in the format string. The "%" character is required before format specifier characters.

Specifier	Description
%a	Abbreviated weekday name (Sun..Sat)
%b	Abbreviated month name (Jan..Dec)
%c	Month, numeric (0..12)
%D	Day of the month with English suffix (0th, 1st, 2nd, 3rd, ...)
%d	Day of the month, numeric (00..31)
%e	Day of the month, numeric (0..31)
%f	Microseconds (000000..999999)
%H	Hour (00..23)
%h	Hour (01..12)
%I	Hour (01..12)
%i	Minutes, numeric (00..59)
%j	Day of year (001..366)
%k	Hour (0..23)
%l	Hour (1..12)
%M	Month name (January..December)
%m	Month, numeric (00..12)
%p	AM or PM
%r	Time, 12-hour (hh:mm:ss followed by AM or PM)
%S	Seconds (00..59)
%s	Seconds (00..59)
%T	Time, 24-hour (hh:mm:ss)
%U	Week (00..53), where Sunday is the first day of the week
%u	Week (00..53), where Monday is the first day of the week
%V	Week (01..53), where Sunday is the first day of the week; used with %X
%v	Week (01..53), where Monday is the first day of the week; used with %x
%W	Weekday name (Sunday..Saturday)
%w	Day of the week (0=Sunday..6=Saturday)
%X	Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V
%x	Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v
%Y	Year, numeric, four digits
%y	Year, numeric (two digits)
%%	A literal "%" character
%x	x, for any "x" not listed above

Practice #10-1
Practice #10-2
Practice #10-3
Practice #10-4
Practice #10-5
Practice #10-6
Practice #10-7
Practice #10-8
Practice #10-9
Practice #10-10
Practice #10-11

DATE_SUB(date,INTERVAL expr unit)

Subtract two dates. See the description for DATE_ADD for similar usages for function arguments.

DATE(expr)

Extracts the date part of the date or datetime expression expr. See Practice #12.

DATEDIFF(expr1,expr2)

Returns expr1 – expr2 expressed as a value in days from one date to the other. expr1 and expr2 are date or date-and-time expressions. Only the date part of the values are used in the calculation. Time part is ignored.

See Practice #13-1 and Practice #13-2.

DAY(date)

Synonym for DAYOFMONTH

DAYNAME(date)

Return the name of the weekday. See Practice #15.

DAYOFMONTH(date)

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

DAYOFWEEK(date)

Returns the weekday index for date (1 = Sunday, 2 = Monday, …, 7 = Saturday). These index values correspond to the ODBC standard. See Practice #17.

DAYOFYEAR(date)

Returns the day of the year for date, in the range 1 to 366. See Practice #18.

EXTRACT(unit FROM date)

This function uses the same kinds of unit specifiers as DATE_ADD, but extracts parts from the date rather than performing date arithmetic.

Practice #19-1
Practice #19-2
Practice #19-3
Practice #19-4

FROM_DAYS(N)

Given a day number N, returns a DATE value. Day number starts from 366 which corresponds to 0001-01-01.

Practice #20-1
Practice #20-2

FROM_UNIXTIME(unix_timestamp)
FROM_UNIXTIME(unix_timestamp,format)

Returns a representation of the unix_timestamp argument as a value in YYYY-MM-DD HH:MM:SS format (Practice #21-1) or YYYYMMDDHHMMSS.uuuuuu format (See Practice #21-2), depending on whether the function is used in a string or numeric context. The value is expressed in the current time zone.

unix_timestamp is an internal timestamp value such as is produced by the UNIX_TIMESTAMP() function. See more information about Unix timestamp in article How to use Date and Time data as integer value in PHP and MySQL.

If format is given, the result is formatted according to the format string, which is used the same way as listed in the entry for the DATE_FORMAT function. See Practice #21-3.

GET_FORMAT(DATE|TIME|DATETIME, 'EUR'|'USA'|'JIS'|'ISO'|'INTERNAL')

Returns a format string such as %m.%d.%Y that can be used with other two functions - DATE_FORMAT and the STR_TO_DATE functions.

See Practice #22.

Here are the results of what you get when run the SELECT statements.

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

HOUR(time)

Returns the hour part from a time string 'HH:MM:SS'. The range of the return value is 0 to 23 for time-of-day values. See Practice #23-1.

However, the range of TIME values actually is much larger, so HOUR can return values greater than 23. See Practice #23-2

LAST_DAY(date)

Returns last day of the month for the date argument.

It takes a date or datetime value and returns the corresponding value for the last day of the month. See Practice #24-1

Returns NULL if the argument is invalid. See Practice #24-2

LOCALTIME
LOCALTIME()

Synonym for NOW

LOCALTIMESTAMP
LOCALTIMESTAMP()

Synonym for NOW

MAKEDATE(year,dayofyear)

Returns a date, given year and day of year values. dayofyear must be greater than 0 or the result is NULL. See Practice #27.

MAKETIME(hour,minute,second)

Returns a time value calculated from the hour, minute, and second arguments. See Practice #28.

Practice #10-1: Using DATE_FORMAT with abbreviation

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.

-- Abbreviated weekday name, month name, and two digits year
SELECT DATE_FORMAT('1997-10-04 22:23:00', '%a, %d %b %y');

Query result set - 1 row returned:
Using DATE_FORMAT with abbreviation

Practice #10-2: Using DATE_FORMAT without abbreviation

/*
Full weekday name, day of the month with English 
suffix, month name, and four digits year.
*/
SELECT DATE_FORMAT('1997-10-04 22:23:00', '%W, %D %M %Y');

Query result set - 1 row returned:
Using DATE_FORMAT without abbreviation

Practice #10-3: Using DATE_FORMAT for American date format

/*
Full weekday name, day of the month with English suffix, 
month name, and four digits year in American date format.
*/
SELECT DATE_FORMAT('1997-10-04 22:23:00', '%W, %m/%d/%Y');

Query result set - 1 row returned:
Using DATE_FORMAT for American date format

Practice #10-4: Using DATE_FORMAT to format hour, minute, second

-- The two queries return the same result
SELECT DATE_FORMAT('1997-10-04 22:23:00', '%H:%i:%s');
 
SELECT DATE_FORMAT('1997-10-04 22:23:00', '%T');

Query result set - 1 row returned:
Using DATE_FORMAT to format hour, minute, second

Practice #10-4: Using DATE_FORMAT to format hour, minute, second in 24-hour fashion

-- The two queries return the same result in 24-hour fashion
SELECT DATE_FORMAT('1997-10-04 22:23:00', '%H:%i:%s');
 
SELECT DATE_FORMAT('1997-10-04 22:23:00', '%T');

Query result set - 1 row returned:
Using DATE_FORMAT to format hour, minute, second in 24-hour fashion

Practice #10-5: Using DATE_FORMAT to format hour, minute, second in AM or PM fashion

-- The two queries return the same result in AM/PM fashion
SELECT DATE_FORMAT('1997-10-04 22:23:00', '%h:%i:%s %p');
 
SELECT DATE_FORMAT('1997-10-04 22:23:00', '%r');

Query result set - 1 row returned:
Using DATE_FORMAT to format hour, minute, second in AM or PM fashion

Practice #10-6: Using DATE_FORMAT to format microseconds

-- Get microseconds
SELECT DATE_FORMAT('1997-10-04 22:23:00', '%f');

Query result set - 1 row returned:
Using DATE_FORMAT to format microseconds

Practice #10-7: Using DATE_FORMAT to get week number where Sunday is the first day

-- Get week number where Sunday is the first day of the week
SELECT DATE_FORMAT('1997-10-04 22:23:00', '%U');

Query result set - 1 row returned:
Using DATE_FORMAT to get week number where Sunday is the first day

Practice #10-8: Using DATE_FORMAT to get week number where Monday is the first day

-- Get week number where Monday is the first day of the week
SELECT DATE_FORMAT('1997-10-04 22:23:00', '%u');

Query result set - 1 row returned:
Using DATE_FORMAT to get week number where Monday is the first day

Practice #10-9: Using DATE_FORMAT to get day of the week (0=Sunday..6=Saturday)

-- Day of the week (0=Sunday..6=Saturday)
SELECT DATE_FORMAT('1997-10-04 22:23:00', '%w');

Query result set - 1 row returned:
Using DATE_FORMAT to get day of the week (0=Sunday..6=Saturday)

Practice #10-10: Using DATE_FORMAT to get week number and year, where Sunday is the first day

/*
%V is Week number (01..53), where Sunday is the first 
day of the week; used with %X.
 
%X is Year for the week where Sunday is the first day 
of the week, numeric, four digits; used with %V
*/
SELECT DATE_FORMAT('1997-10-04 22:23:00', 'Week %V of %X');

Query result set - 1 row returned:
Using DATE_FORMAT to get week number and year, where Sunday is the first day

Practice #10-11: Using DATE_FORMAT to get week number and year, where Monday is the first day

/*
%v is Week number (01..53), where Monday is the first 
day of the week; used with %x
 
%x is Year for the week, where Monday is the first day 
of the week, numeric, four digits; used with %v
*/
SELECT DATE_FORMAT('1997-10-04 22:23:00', 'Week %v of %x');

Query result set - 1 row returned:
Using DATE_FORMAT to get week number and year, where Monday is the first day

Practice #12: Using DATE function

/*
Extracts the date part of the date or 
datetime expression. 
*/
SELECT DATE('2003-12-31 01:02:03');

Query result set - 1 row returned:
Using DATE function

Practice #13-1: Using DATEDIFF function to get a positive number

/*
The result is a positive number
*/
SELECT DATEDIFF('1997-12-31 23:59:59','1997-12-30');

Query result set - 1 row returned:
Using DATEDIFF function to get a positive number

Practice #13-2: Using DATEDIFF function to get a negative number

/*
The result is a negative number
*/
SELECT DATEDIFF('1997-11-30 23:59:59','1997-12-31');

Query result set - 1 row returned:
Using DATEDIFF function to get a negative number

Practice #15: Using DAYNAME function to get the name of the weekday

-- Returns the name of the weekday 
SELECT DAYNAME('1998-02-05');

Query result set - 1 row returned:
Using DAYNAME function to get the name of the weekday

Practice #16: Using DAYOFMONTH function to get day of the month

-- Returns the day of the month 1 to 31
SELECT DAYOFMONTH('1998-02-03');

Query result set - 1 row returned:
Using DAYOFMONTH function to get day of the month

Practice #17: Using DAYOFWEEK function

/*
Returns the weekday index for date 
(1 = Sunday, 2 = Monday, ..., 7 = Saturday)
*/
SELECT DAYOFWEEK('1998-02-03');

Query result set - 1 row returned:
Using DAYOFWEEK function

Practice #18: Using DAYOFYEAR function

-- Returns the day of the year (1-366)
SELECT DAYOFYEAR('1998-02-03');

Query result set - 1 row returned:
Using DAYOFYEAR function

Practice #19-1: Using EXTRACT function to get year part

-- Extract year part
SELECT EXTRACT(YEAR FROM '1999-07-02');

Query result set - 1 row returned:
Using EXTRACT function to get year part

Practice #19-2: Using EXTRACT function to get year and month part

-- Extract year and month
SELECT EXTRACT(YEAR_MONTH FROM '1999-07-02 01:02:03');

Query result set - 1 row returned:
Using EXTRACT function to get year and month part

Practice #19-3: Using EXTRACT function to get day, hour, minute

-- Extract day, hour, minute
SELECT EXTRACT(DAY_MINUTE FROM '1999-07-02 01:02:03');

Query result set - 1 row returned:
Using EXTRACT function to get day, hour, minute

Practice #19-4: Using EXTRACT function to get microsecond

-- Extract microsecond part
SELECT EXTRACT(MICROSECOND FROM '2003-01-02 10:30:00.000123');

Query result set - 1 row returned:
Using EXTRACT function to get microsecond

Practice #20-1: Using FROM_DAYS function

-- Day number 366 corresponds to 0001-01-01
SELECT FROM_DAYS(366);

Query result set - 1 row returned:
Using FROM_DAYS function

Practice #20-2: Using FROM_DAYS function

SELECT FROM_DAYS(729669);

Query result set - 1 row returned:
Using FROM_DAYS function

Practice #21-1: Using FROM_UNIXTIME function to convert Unix timestamp value to 'YYYY-MM-DD HH:MM:SS' format

-- Convert Unix timestamp value to 'YYYY-MM-DD HH:MM:SS' format
SELECT FROM_UNIXTIME(1196440219);

Query result set - 1 row returned:
Using FROM_UNIXTIME function to convert Unix timestamp value to 'YYYY-MM-DD HH:MM:SS' format

Practice #21-2: Using FROM_UNIXTIME function to convert Unix timestamp value to YYYYMMDDHHMMSS.uuuuuu format

/*
Convert Unix timestamp value to 'YYYYMMDDHHMMSS.uuuuuu' 
format in arithmetic operation 
*/
SELECT FROM_UNIXTIME(1196440219) + 0;

Query result set - 1 row returned:
Using FROM_UNIXTIME function to convert Unix timestamp value to YYYYMMDDHHMMSS.uuuuuu format

Practice #21-3: Using FROM_UNIXTIME function with format

-- Format the converted date and time
SELECT FROM_UNIXTIME('2007-11-30 10:30:19',
    '%D %M %Y %h:%i:%s %x') AS new_date;

Query result set - 1 row returned:
Using FROM_UNIXTIME function with format

Practice #22: Using GET_FORMAT function with DATE_FORMAT function

/*
These two queries return the same result.
*/
 
-- Format a date by using GET_FORMAT function.
SELECT DATE_FORMAT('2003-10-03',GET_FORMAT(DATE,'EUR'));
 
-- Format a date by using a format string
SELECT DATE_FORMAT('2003-10-03','%d.%m.%Y');

Query result set - 1 row returned:
Using GET_FORMAT function with DATE_FORMAT function

Practice #23-1: Using HOUR function to get a value from 0 to 23.

-- Get hour part from a time string
SELECT HOUR('10:05:03');

Query result set - 1 row returned:
Using HOUR function to get a value from 0 to 23.

Practice #23-2: Using HOUR function to get a value greater than 23.

-- The hour part is greater than 23
SELECT HOUR('272:59:59');

Query result set - 1 row returned:
Using HOUR function to get a value greater than 23.

Practice #24-1: Using LAST_DAY function.

-- Get last day of the month
SELECT LAST_DAY('2004-02-05');

Query result set - 1 row returned:
Using LAST_DAY function

Practice #24-2: Using LAST_DAY function with invalid argument.

-- Returns NULL because 2003-03-32 is invalid
SELECT LAST_DAY('2003-03-32');

Query result set - 1 row returned:
Using LAST_DAY function with invalid argument

Practice #27: Using MAKEDATE function.

/*
Create a date in YYYY-MM-DD format by giving 
the year and day of year value.
*/
SELECT MAKEDATE(2001,31), MAKEDATE(2001,32), MAKEDATE(2001,365);

Query result set - 1 row returned:
Using MAKEDATE function

Practice #28: Using MAKETIME function.

/*
Create time in HH:MM:SS format by giving 
hour, minute, and second value.
*/
SELECT MAKETIME(12,15,30);

Query result set - 1 row returned:
Using MAKETIME function

Next tutorial: Using Date and Time Functions, Part 3
Back to Section Index: Single Row Functions
Back to Tutorial Index: MySQL Database How-to Articles and Tutorials

	Custom Search