Custom Search
 


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:

  1. Part 1 of MySQL Date and Time Functions
  2. Part 3 of MySQL Date and Time Functions

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

No. Name Description
10DATE_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

11DATE_SUB(date,INTERVAL expr unit)Subtract two dates. See the description for DATE_ADD for similar usages for function arguments.
12DATE(expr)Extracts the date part of the date or datetime expression expr. See Practice #12.
13DATEDIFF(expr1,expr2)Returns expr1expr2 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.

14DAY(date)Synonym for DAYOFMONTH
15DAYNAME(date)Return the name of the weekday. See Practice #15.
16DAYOFMONTH(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.
17DAYOFWEEK(date)Returns the weekday index for date (1 = Sunday, 2 = Monday, …, 7 = Saturday). These index values correspond to the ODBC standard. See Practice #17.
18DAYOFYEAR(date)Returns the day of the year for date, in the range 1 to 366. See Practice #18.
19EXTRACT(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

20FROM_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

21FROM_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.

22GET_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

23HOUR(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

24LAST_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

25LOCALTIME
LOCALTIME()
Synonym for NOW
26LOCALTIMESTAMP
LOCALTIMESTAMP()
Synonym for NOW
27MAKEDATE(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.
28MAKETIME(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

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

Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.

/*
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

Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.

/*
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

Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.

-- 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

Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.

-- 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

Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.

-- 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

Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.

-- 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

Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.

-- 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

Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.

-- 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)

Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.

-- 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

Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.

/*
%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

Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.

/*
%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

Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.

/*
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

Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.

/*
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

Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.

/*
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

Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.

-- 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

Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.

-- 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

Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.

/*
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

Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.

-- 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

Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.

-- 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

Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.

-- 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

Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.

-- 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

Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.

-- 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

Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.

-- 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

Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.

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

Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.

-- 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

Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.

/*
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

Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.

-- 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

Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.

/*
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.

Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.

-- 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.

Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.

-- 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.

Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.

-- 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.

Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.

-- 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.

Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.

/*
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.

Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.

/*
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





Other tutorials in this category

1. Using String Functions, Part 1

2. Using String Functions, Part 2

3. Using String Functions, Part 3

4. Using MySQL Date and Time Functions, Part 1

5. Using MySQL Date and Time Functions, Part 3

6. Using MySQL Control Flow Functions

7. Using MySQL Conversion Functions

8. Using MySQL Comparison Functions

9. Using Aggregate Functions in MySQL

10. MySQL RANK() function

11. MySQL DENSE_RANK() function

12. MySQL ROW_NUMBER() function

13. Compare RANK(), DENSE_RANK(), and ROW_NUMBER() function

14. Using LIMIT and OFFSET to Page Thru Query Result in MySQL

15. Using LIMIT, SQL_CALC_FOUND_ROWS and FOUND_ROWS() to Page Thru Query Result in MySQL

16. Use LEAST function to find the SMALLEST value from multiple arguments

17. How to make case-sensitive comparison in MySQL

18. Use GREATEST function to find the LARGEST value from multiple arguments

19. MySQL MID() function with examples

20. MySQL LOCATE() function with examples

21. MySQL SUBSTR() function with examples

22. MySQL POSITION() function with examples

23. How to use MySQL REPLACE function in SELECT and UPDATE queries

Back to Tutorial Index Page


Copyright © 2024 GeeksEngine.com. All Rights Reserved.

This website is hosted by HostGator.

No portion may be reproduced without my written permission. Software and hardware names mentioned on this site are registered trademarks of their respective companies. Should any right be infringed, it is totally unintentional. Drop me an email and I will promptly and gladly rectify it.

 
Home | Feedback | Terms of Use | Privacy Policy