w3resource logo


gallery w3resource

MySQL Date and Time Functions, slides presentation

Secondary Nav

 

This presentation describes MySQL Date and Time functions with syntax.examples and explanation.

Transcript

MySQL Date & Time Functions

ADDDATE()

MySQL ADDDATE() adds a time value with a date.

Syntax : ADDDATE(date,INTERVAL expr unit), ADDDATE(expr,days)

Example :
SELECT ADDDATE('2008-05-15', INTERVAL 10 DAY) as required_date;

Output :

required_date
--------------
2008-05-25

ADDTIME()

In MySQL the ADDTIME() returns a time or datetime after adding a time value with a time or datetime.

Syntax : ADDTIME(expr1,expr2)

Example :
SELECT ADDTIME('2008-05-15 13:20:32.50','2 1:39:27.50') as required_datetime;

Output :

required_datetime
--------------------------
2008-05-17 15:00:00.000000

CONVERT_TZ()

In MySQL the CONVERT_TZ() returns a resulting value after converting a datetime value from a time zone specified as the second argument to the time zone specified as the third argument. This function returns NULL when the arguments are invalid.

Syntax : CONVERT_TZ (dt, from_tz,to_tz)

Example :
SELECT CONVERT_TZ('2008-05-15 12:00:00','+00:00','+10:00');

Output :

CONVERT_TZ('2008-05-15 12:00:00','+00:00','+10:00')
----------------------------------------------------
2008-05-15 22:00:00

CURDATE(),CURRENT_DATE()

In MySQL the CURDATE() returns the current date in 'YYYY-MM-DD' format or 'YYYYMMDD' format depending on whether numeric or string is used in the function.

Syntax : CURDATE(), CURRENT_DATE()

Example :
SELECT CURDAE();

Output :

CURDATE()
------------
2014-08-05

CURRENT_TIME

In MySQL the CURRENT_TIME() returns the current time in ‘HH:MM:SS’ format or HHMMSS.uuuuuu format depending on whether numeric or string is used in the function

Syntax : CURRENT_TIME(), CURRENT_TIME

Example :
SELECT CURRENT_TIME;

Output :

CURRENT_TIME
-------------
11:19:11

CURRENT_TIMESTAMP()

In MySQL the CURRENT_TIMESTAMP returns the current date and time in ‘YYYY-MM-DD HH:MM:SS’ format or YYYYMMDDHHMMSS.uuuuuu format depending on whether numeric or string is used in the function.

Syntax : CURRENT_TIMESTAMP

Example :
SELECT CURRENT_TIMESTAMP;

Output :

CURRENT_TIMESTAMP
-------------------
2014-08-05 11:27:50

CURTIME()

In MySQL the CURRENT_TIME() returns the current time in ‘HH:MM:SS’ format or HHMMSS.uuuuuu format depending on whether numeric or string is used in the function

Syntax : CURTIME()

Example :
SELECT CURTIME();

Output :

CURTIME()
----------
11:19:11

DATE_ADD()

MySQL DATE_ADD() adds time values (as intervals) to a date value. The ADDDATE() is the synonym of DATE_ADD().

Syntax : DATE_ADD(date,INTERVAL expr unit)

Example :
SELECT DATE_ADD('2008-05-15', INTERVAL 10 DAY) as required_date;

Output :

required_date
--------------
2008-05-25

-table of format specifiers -

Name      Description
%a        Abbreviated weekday name (Sun..Sat)
%b        Abbreviated month name (Jan..Dec)
%ac       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

DATE_FORMAT()

MySQL DATE_FORMAT() formats a date as specified in the argument. The ‘%’ is required before the format specifier characters.

Syntax : DATE_FORMAT(date,format)

Example :
To get week day name, day of the month with english suffix, month name and year in numeric %W %D %M %Y specifier can be used.
SELECT DATE_FORMAT('2008-05-15 22:23:00', '%W %D %M %Y');

Output :

DATE_FORMAT('2008-05-15 22:23:00', '%W %D %M %Y')
-------------------------------------------------
Thursday 15th May 2008

Example :
To get the time in 12-hour format followed by AM or PM %r specifier can be used
SELECT DATE_FORMAT('2008-05-15 22:23:00', '%r');

Output :

DATE_FORMAT('2008-05-15 22:23:00', '%r')
-----------------------------------------
10:23:00 PM

DATE_SUB()

MySQL DATE_SUB() function subtract a time value (as interval) from a date.

Syntax : DATE_SUB(date, INTERVAL expr unit)

Example :
This example subtracting day from a data.
SELECT DATE_SUB('2008-05-15', INTERVAL 10 DAY);

Output :

DATE_SUB('2008-05-15', INTERVAL 10 DAY)
----------------------------------------
2008-05-05

Example :
This example subtracting a date time from a data.
SELECT DATE_SUB('2008-05-15 4:50:20',INTERVAL '1 1:10:10' DAY_SECOND);

Output :

DATE_SUB('2008-05-15 4:50:20',INTERVAL '1 1:10:10' DAY_SECOND)
--------------------------------------------------------------
2008-05-14 03:40:10

DATE()

MySQL DATE() takes the DATE part out from a DATEtime expression.

Syntax : DATE_SUB(expr)

Example :
This example extract the DATE portion from the specified DATEtime.
SELECT DATE('2008-05-17 11:31:31') as required_DATE;

Output :

required_DATE
--------------
2008-05-17

DATEDIFF()

MySQL DATEDIFF() returns the number of days between two dates or datetimes. This function only calculates the date portion from each expression.

Syntax : DATEDIFF(expr1,expr2)

Example :
This example returns the difference between two date.
SELECT DATEDIFF('2008-05-17 11:31:31','2008-04-28');

Output :

DATEDIFF('2008-05-17 11:31:31','2008-04-28')
--------------------------------------------
                                      19

DAY()

MySQL DAY() returns the day of the month for a specified date. The day returned will be within the range of 1 to 31. If the given date is ‘0000-00-00’, the function will return 0.

Syntax : DAY(date)

Example :
The following statement will return the day of the month from the specified date 2008-05-15.
SELECT DAY('2008-05-15');

Output :

DAY('2008-05-15')
-----------------
             15

DAYNAME()

MySQL DAYNAME() returns the name of the week day of a date,.

Syntax : DAYNAME(date1)

Example :
The statement below returns the name of the week day of the specified date.
SELECT DAYNAME('2008-05-15');

Output :

DAYNAME('2008-05-15')
---------------------
Thursday

DAYOFMONTH()

MySQL DAYOFMONTH() returns the day of the month for a specified date.

Syntax : DAYOFMONTH(date)

Example :
The following statement will return the day of the month from the specified date 2008-05-15.
SELECT DAYOFMONTH('2008-05-15');

Output :

DAYOFMONTH('2008-05-15')
------------------------
                    15

DAYOFWEEK()

MySQL DAYOFWEEK() returns the week day number (1 for Sunday,2 for Monday …… 7 for Saturday ) for a specific date.

Syntax : DAYOFWEEK(date)

Example :
The following statement will return the week day number of the specific date.
SELECT DAYOFWEEK('2008-05-15');

Output :

DAYOFWEEK('2008-05-15')
-----------------------
                    5

DAYOFYEAR()

MySQL DAYOFYEAR() returns day of the year for a date. The return value is within the range of 1 to 366.

Syntax : DAYOFYEAR(date1)

Example :
The following statement will return the day of year from the given date.
SELECT DAYOFWEEK('2008-05-15');

Output :

DAYOFYEAR('2008-05-15')
-----------------------
                  136

EXTRACT()

MySQL EXTRACT() EXTRACTs a part of a given date. This function does not perform date arithmetic.

Syntax : EXTRACT(unit FROM date1)

Example :
The following statement will EXTRACT the year part from the date 2008-05-15.
SELECT EXTRACT(YEAR FROM '2008-05-15')

Output :

EXTRACT(YEAR FROM '2008-05-15')
-------------------------------
                          2008

Example :
The following statement will EXTRACT the HOUR_SECOND part from 2008-05-15 15:53:20.
SELECT EXTRACT(HOUR_SECOND FROM '2008-05-15 15:53:20');

Output :

EXTRACT(HOUR_SECOND FROM '2008-05-15 15:53:20')
-----------------------------------------------
                                        155320

The following statement will EXTRACT the year and month part from 2008-05-15 15:53:20.
SELECT EXTRACT(YEAR_MONTH FROM '2008-05-15 15:53:20')

Output :

EXTRACT(YEAR_MONTH FROM '2008-05-15 15:53:20')
----------------------------------------------
                                       200805

FROM_DAYS()

MySQL FROM_DAYS() returns a date against a datevalue.

Syntax : EXTRACT(unit FROM date1)

Example :
The following statement will return a date against the datevalue 733910.
SELECT FROM_DAYS(733910);

Output :

FROM_DAYS(733910)
-----------------
2009-05-18

FROM_UNIXTIME()

MySQL FROM_UNIXTIME() returns a date /datetime from a version of unix_timestamp. The return value format depending upon the context of the function ( whether numeric or string). If specified, the result is formatted according to a given format string.

Syntax : FROM_ UNIXTIME (unix_timestamp, [format ])

Example :
The following statement will return a date time value from 1255033470.
SELECT FROM_UNIXTIME(1255033470);

Output :

FROM_UNIXTIME(1255033470)
-------------------------
2009-10-09 01:54:30

Example :
The following statement will return a date time value from 1255033470.
SELECT FROM_UNIXTIME(1255033470)+0;

Output :

FROM_UNIXTIME(1255033470)+0
20091009015430.000000

Example :
The following statement will return a date time value according to the format string %Y %D %M %h:%i:%s %x.
SELECT FROM_UNIXTIME(1255033470, '%Y %D %M %h:%i:%s %x');

Output :

FROM_UNIXTIME(1255033470, '%Y %D %M %h:%i:%s %x')
-------------------------------------------------
2009 9th October 01:54:30 2009

GET_FORMAT()

MySQL GET_FORMAT() converts a date or time or datetime in a formatted manner.

Syntax : GET_ FORMAT([date | time | datetime ],[‘EUR’ |‘USA’ |’JIS’|’ISO’| ‘INTERNAL’])

Example :
The following statement will arrange the date format in EUR.
SELECT GET_FORMAT(DATE,'EUR');

Output :

GET_FORMAT(DATE,'EUR')
----------------------
%d.%m.%Y

Example :
The following statement will format and return the specified date 2009-05-18 in the format obtained from GET_FORMAT(DATE,'EUR').
SELECT DATE_FORMAT('2009-05-18',GET_FORMAT(DATE,'EUR'));

Output :

DATE_FORMAT('2009-05-18',GET_FORMAT(DATE,'EUR'))
------------------------------------------------
18.05.2009

Example :
The following statement will format and return the specified time 11:15:46 PM in a specific format as obtained from STR_TO_DATE(TIME,'USA').
SELECT STR_TO_DATE('11:15:46 PM',GET_FORMAT(TIME,'USA'));

Output :

STR_TO_DATE('11:15:46 PM',GET_FORMAT(TIME,'USA'))
-------------------------------------------------
23:15:46

HOUR()

MySQL HOUR() returns the HOUR of a time. The return value is within the range of 0 to 23 for time-of-day values. The range of time values may be larger than 23.

Syntax : HOUR(time)

Example :
The following statement return the HOUR from the given time 15:13:46.
SELECT HOUR('15:13:46');

Output :

HOUR('15:13:46')
-----------------
15

LAST_DAY()

MySQL LAST_DAY() returns the last day of the corresponding month for a date or datetime value. If the date or datetime value is invalid, the function returns NULL.

Syntax : LAST_DAY(date1)

Example :
The following statement will return the last date of the corresponding month of the given date 2009-05-18.
SELECT LAST_DAY('2009-05-18');

Output :

LAST_DAY('2009-05-18')
----------------------
2009-05-31

LOCALTIME()

MySQL LOCALTIME returns the value of current date and time in various format depending on the context of the function.

Syntax : LOCALTIME

Example :
The statement below will return the current date and time in ‘YYYY-MM-DD HH:SS:MM’ format.
SELECT LOCALTIME;

Output :

LOCALTIME
-------------------
2014-08-08 09:35:56

LOCALTIMESTAMP()

MySQL LOCALTIMESTAMP returns the value of current date and time in various format depending on the context of the function.

Syntax : LOCALTIMESTAMP

Example :
The statement below will return the current date and time in ‘YYYY-MM-DD HH:SS:MM’ format.
SELECT LOCALTIMESTAMP;

Output :

LOCALTIMESTAMP
-------------------
2014-08-08 09:42:16

MAKEDATE()

MySQL MAKEDATE() returns a date by taking a value of a year and a number of days. The number of days must be greater than 0 other wise a NULL will be returned.

Syntax : MAKEDATE(year,dayofyear);

Example :
The statement below will make a date from year 2009 and number of days 138.
SELECT MAKEDATE(2009,138);

MAKEDATE(2009,138)
------------------
2009-05-18

MAKETIME()

MySQL MAKETIME() makes and returns a time value from a given hour, minute and seconds. The value of hour may be greater than 24 but value of minutes and second greater than 59 return NULL.

Syntax : MAKETIME(hour,minute,second);

Example :
The statement below will make a time value from 15 hours, 25 minutes and 36 seconds.
SELECT MAKETIME(15,25,36);

Output :

MAKETIME(15,25,36)
-------------------
15:25:36

MICROSECOND()

MySQL MICROSECOND() returns MICROSECONDs from the time or datetime expression. The return value is within the range of 0 to 999999.

Syntax : MICROSECOND(expr)

Example :
The following statement will return MICROSECONDs from the datetime 2009-05-18 10:15:21.000423.
SELECT MICROSECOND('2009-05-18 10:15:21.000423');

Output :

MICROSECOND('2009-05-18 10:15:21.000423')
------------------------------------------
423

MINUTE()

MySQL MINUTE() returns a MINUTE from a time or datetime value.

Syntax : MINUTE(time)

Example :
The following statement will return MINUTEs from the given datetime 2009-05-18 10:15:21.000423.
SELECT MINUTE('2009-05-18 10:15:21.000423');

Output :

MINUTE('2009-05-18 10:15:21.000423')
-------------------------------------
15

MONTH()

MySQL MONTH() returns the MONTH for the date within a range of 1 to 12 ( January to December). It Returns 0 when MONTH part for the date is 0..

Syntax : MONTH(date)

Example :
The following statement will return the MONTH for the given date 2009-05-18.
SELECT MONTH('2009-05-18');

Output :

MONTH('2009-05-18')
-------------------
5

MONTHNAME()

MySQL MONTHNAME() returns the full name of the month for a given date. The return value is within the range of 1 to 12 ( January to December). It Returns NULL when month part for the date is 0 or more than 12

Syntax : MONTHNAME(date)

Example :
The following statement will return the full name of the month for the given date 2009-05-18.
SELECT MONTHNAME('2009-05-18');

Output :

MONTHNAME('2009-05-18')
-----------------------
May

NOW()

MySQL NOW() returns the value of current date and time in ‘YYYY-MM-DD HH: MM:SS’ format or YYYYMMDDHHMMSS.uuuuuu format depending on the context (numeric or string) of the function.

Syntax : NOW()

Example :
The following statement will return the current date and time in ‘YYYY-MM-DD HH:SS:MM’ format.
SELECT NOW();

Output :

NOW()
-------------------
2014-08-08 10:04:25

Example :
The following statement will return the date and current time in ‘YYYY-MM-DD HH:SS:MM’ format for the previous day. The keyword ‘INTERVAL’ have been introduced to get the result.
SELECT NOW(),NOW()-INTERVAL 1 DAY;

Output :

NOW()               NOW()-INTERVAL 1 DAY
--------------------------------------------
2014-08-08 10:10:13 2014-08-07 10:10:13

Example :
The following statement will return the date and time in ‘YYYY-MM-DD HH:SS:MM’ format before 1 hour of current datetime.
SELECT NOW(),NOW()-INTERVAL 1 HOUR;

Output :

NOW()               NOW()-INTERVAL 1 HOUR
------------------------------------------
2014-08-08 10:14:56 2014-08-08 09:14:56

PERIOD_ADD()

MySQL PERIOD_ADD() adds a number of months with a period and returns the value in the format YYYYMM OR YYMM. Remember that the format YYYYMM and YYMM are not date values.

Syntax : PERIOD_ADD(P,N);

Example :
The following statement will return a value in YYYYMM or YYMM format after adding 13 months with the period 200905.
SELECT PERIOD_ADD(200905,13);

Output :

PERIOD_ADD(200905,13)
----------------------
201006

PERIOD_DIFF()

MySQL PERIOD_DIFF() returns the difference between two periods. Periods should be in the same format i.e. YYYYMM or YYMM. It is to be noted that periods are not date values.

Syntax : PERIOD_DIFF(P1,P2);

Example :
The following statement will return a value in YYYYMM or YYMM format after calculating the difference between two periods 200905 and 200811.
SELECT PERIOD_DIFF(200905,200811);

Output :

PERIOD_DIFF(200905,200811)
--------------------------
6

QUARTER()

MySQL QUARTER() returns the quarter of the year for a date. The return value is in the range of 1 to 4.

Syntax : QUARTER(date)

Example :
The following statement will return a value between 1 to 4 as a QUARTER of a year for a given date 2009-05-18.
SELECT QUARTER('2009-05-18');

Output :

QUARTER('2009-05-18')
----------------------
2

SEC_TO_TIME()

MySQL SEC_TO_TIME() returns a time value by converting the seconds specified in the argument. The return value is in hours, minutes and seconds. The range of the result is in the time data type.

Syntax : SEC_TO_TIME(seconds)

Example :
The following statement will return a time value after converting the seconds value 3610.
SELECT SEC_TO_TIME(3610);

Output :

SEC_TO_TIME(3610)
-----------------
01:00:10

SECOND()

MySQL SECOND() returns the second for a time. The return value is in the range of 0 to 59.

Syntax : SECOND(time)

Example :
The following statement will return the value of second for a given time 21:29:46.
SELECT SECOND('21:29:46');

Output :

SECOND('21:29:46')
------------------
46

STR_TO_DATE()

MySQL STR_TO_DATE() returns a datetime value by taking a string and a specific format string as arguments. If the date or time or datetime value specified as string is illegal, the function returns NULL.

Syntax : STR_TO_DATE(str,format)

Example :
The following statement will return a valid date from the given string 18,05,2009 according to the format %d,%m,%Y.
SELECT STR_TO_DATE('18,05,2009','%d,%m,%Y');

Output :

STR_TO_DATE('18,05,2009','%d,%m,%Y')
2009-05-18

Example :
The following statement will return a valid date from the given string May 18, 2009 according to the format %M %d,%Y.
SELECT STR_TO_DATE('May 18, 2009','%M %d,%Y');

Output :

STR_TO_DATE('May 18, 2009','%M %d,%Y')
--------------------------------------
2009-05-18

Example :
The following statement will return a valid time from the given string 11:59:59 according to the format %h:%i:%s.
SELECT STR_TO_DATE('11:59:59','%h:%i:%s');

Output :

STR_TO_DATE('11:59:59','%h:%i:%s')
-----------------------------------
11:59:59

SUBDATE()

MySQL SUBDATE() subtracts a time value (as interval) from a given date.

Syntax : SUBDATE(date, INTERVAL expr unit)

Example :
The following statement will return a date after subtracting 10 days (notice that INTERVAL keyword is used) from the specified date 2008-05-15.
SELECT SUBDATE('2008-05-15', INTERVAL 10 DAY);

Output :

SUBDATE('2008-05-15', INTERVAL 10 DAY)
2008-05-05

Example :
The following statement will return a date after subtracting 10 days (notice that INTERVAL keyword is not used) from the specified date 2008-05-15.
SELECT SUBDATE('2008-05-15', 10);

Output :

SUBDATE('2008-05-15', 10)
-------------------------
2008-05-05

SUBTIME()

MySQL SUBTIME() subtracts one datetime value from another.

Syntax : SUBTIME(expr1,expr2);

Example :
The following statement will return a datetime value between two datetimes 2009-05-18 10:29:43.999999 and 19 3:31:18.000002 specified in the arguments.
SELECT SUBTIME('2009-05-18 10:29:43.999999','19 3:31:18.000002');

Output :

SUBTIME('2009-05-18 10:29:43.999999','19 3:31:18.000002')
---------------------------------------------------------
2009-04-29 06:58:25.999997

SYSDATE()

MySQL SYSDATE() returns the current date and time in YYYY-MM-DD HH:MM: SS or YYYYMMDDHHMMSS.uuuuuu format depending on the context of the function.

Syntax : SYSDATE()

Example :
The following statement will return current date and time
SELECT SYSDATE();

Output :

          SYSDATE()
-------------------
2014-08-08 11:44:21

TIME_FORMAT()

MySQL TIME_FORMAT() converts a time in a formatted string using the format specifiers.

Syntax : TIME_FORMAT(time,format)

Example :
The following statement will convert a given time string 97:15:40 in to %H %k %h %I %l format.
SELECT TIME_FORMAT('97:15:40','%H %k %h %I %l');

Output :

TIME_FORMAT('97:15:40','%H %k %h %I %l')
----------------------------------------
97 97 01 01 1

TIME_TO_SEC()

MySQL TIME_TO_SEC() converts a time value in to seconds.

Syntax : TIME_TO_SEC(tm)

Example :
The following statement will convert the specified time 05:15:40 in seconds.
SELECT TIME_TO_SEC('05:15:40');

Output :

TIME_TO_SEC('05:15:40')
-----------------------
18940

TIME()

MySQL TIME() extracts the time part of a time or datetime expression as string format.

Syntax : TIME(expr)

Example :
The following statement will return the time portion from the given date-time value 2009-05-18 15:45:57.005678.
SELECT TIME('2009-05-18 15:45:57.005678');

Output :

TIME('2009-05-18 15:45:57.005678')
----------------------------------
15:45:57.005678

TIMEDIFF()

MySQL TIMEDIFF() returns the differences between two time or datetime expressions. It is to be noted that two expressions must be of same type.

Syntax : TIMEDIFF(expr1,expr2)

Example :
The following statement will return the difference between two datetime values 2009-05-18 15:45:57.005678 and 2009-05-18 13:40:50.005670.
SELECT TIMEDIFF('2009-05-18 15:45:57.005678', '2009-05-18 13:40: 50.005670');

Output :

TIMEDIFF('2009-05-18 15:45:57.005678','2009-05-18 13:40:
-------------------------------------------------
50.005670')02:05:07.000008

TIMESTAMP()

MySQL TIMESTAMP() returns a datetime value against a date or datetime expression. If two arguments are used with this function, first it adds the second expression with the first and then returns a datetime.

Syntax : TIMESTAMP(expr); TIMESTAMP(expr1,expr2)

Example :
The following statement will return a datetime value for the given date expression 2009-05-18.
SELECT TIMESTAMP('2009-05-18');

Output :

TIMESTAMP('2009-05-18')
-----------------------
2009-05-18 00:00:00

TIMESTAMPADD()

MySQL TIMESTAMPADD() adds time value with a date or datetime value. The unit for interval as mentioned should be one of the following : FRAC_SECOND (microseconds), SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER or YEAR.

Syntax : TIMESTAMPADD(unit,interval,datetime_expr)

Example :
The following statement will return a date value after adding 2 months with the specified date 2009-05-18.
SELECT TIMESTAMPADD(MONTH,2,'2009-05-18');

Output :

TIMESTAMPADD(MONTH,2,'2009-05-18')
----------------------------------
2009-07-18

TIMESTAMPDIFF()

MySQL the TIMESTAMPDIFF() returns a value after subtracting a datetime expression from another.

Syntax : TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)

Example :
The following statement will return a value in months by subtracting 2009-05-18 from 2009-07-29.
SELECT TIMESTAMPDIFF(MONTH,'2009-05-18','2009-07-29');

Output :

TIMESTAMPDIFF(MONTH,'2009-05-18','2009-07-29')
----------------------------------------------
                                           2

TO_DAYS()

MySQL TO_DAYS() returns number of days between a given date and year 0.

Syntax : TO_DAYS(date)

Example :
The following statement will return the number of days from year 0 to 2009-05-18.
SELECT TO_DAYS('2009-05-18');

Output :

TO_DAYS('2009-05-18')
---------------------
              733910

UNIX_TIMESTAMP()

MySQL UNIX_TIMESTAMP() returns a Unix timestamp in seconds since '1970- 01-01 00:00:00' UTC as an unsigned integer if no arguments are passed with UNIT_TIMESTAMP().

Syntax : UNIX_TIMESTAMP(); UNIX_TIMESTAMP(date)

Example :
The following statement will return the unix timestamp in seconds as an unsigned integer since '1970-01-01 00:00:00' UTC.
SELECT UNIX_TIMESTAMP();

Output :

UNIX_TIMESTAMP()
----------------
     1407482892

UTC_DATE()

MySQL UTC_DATE returns the current UTC (Coordinated Universal Time) date as a value in 'YYYY-MM-DD' or YYYYMMDD format depending on the context of the function i.e. in a string or numeric context.

Syntax : UTC_DATE , UTC_DATE()

Example :
The following statement will return the current UTC date.
SELECT UTC_DATE,UTC_DATE();

Output :

UTC_DATE UTC_DATE()
---------------------
2014-08-08 2014-08-08

UTC_TIME()

MySQL UTC_TIME returns the current UTC time as a value in 'HH:MM:SS' or HHMMSS format depending on the context of the function i.e. in a string or numeric context.

Syntax : UTC_TIME , UTC_TIME()

Example :
The following statement will return the current UTC time.
SELECT UTC_TIME,UTC_TIME();

Output :

UTC_TIME UTC_TIME()
-------------------
07:33:34 07:33:34

UTC_TIMESTAMP()

In MySQL the 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 the usage of the function i.e. in a string or numeric context.

Syntax : UTC_TIMESTAMP, UTC_TIMESTAMP()

Example :
The following statement will return the current UTC date and time.
SELECT UTC_TIMESTAMP,UTC_TIMESTAMP();

Output :

UTC_TIMESTAMP UTC_TIMESTAMP()
---------------------------------------
2014-08-08 07:35:53 2014-08-08 07:35:53

WEEK()

MySQL WEEK() returns the week number for a given date. The argument allows the user 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. If no argument is included with the function, it returns the default week format.

Syntax : WEEK(date[mode]);

Example :
The following statement will return the week of the specified date 2009-05-18.
SELECT WEEK('2009-05-18');

Output :

WEEK('2009-05-18')
------------------
               20

Example :
The following statement will return the week of the specified date 2009-05-18. 1 as the second argument defines that the first day of the week is assumed as Monday.
SELECT WEEK('2009-05-18',1);

Output :

WEEK('2009-05-18',1)
---------------------
                 21

WEEKDAY()

MySQL WEEKDAY() returns the index of the day in a week for a given date (0 for Monday, 1 for Tuesday and ......6 for Sunday).

Syntax : WEEKDAY(date)

Example :
The following statement will returns the index of the week for the date 2009-05-19.
SELECT WEEKDAY('2009-05-19');

Output :

WEEKDAY('2009-05-19')
---------------------
                   1

WEEKOFYEAR()

MySQL WEEKOFYEAR() returns the calender week (as a number) of a given date. The return value is in the range of 1 to 53.

Syntax : WEEKOFYEAR(date)

Example :
The following statement will return the calender week of the specified date 2009-05-19.
SELECT WEEKOFYEAR('2009-05-19');

Output :

WEEKOFYEAR('2009-05-19')
------------------------
                    21

YEAR()

MySQL YEAR() returns the year for a given date. The return value is in the range of 1000 to 9999 or 0 for 'zero' date.

Syntax : YEAR(date)

Example :
The following statement will return the year part of the specified date 2009-05-19.
SELECT YEAR('2009-05-19');

Output :

YEAR('2009-05-19')
------------------
2009

YEARWEEK()

MySQL YEARWEEK() returns year and week number for a given date.

Syntax : YEARWEEK()

Example :
The following statement will return the year and week number of the date 2009-05-18.
SELECT YEARWEEK('2009-05-18');

Output :

YEARWEEK('2009-05-18')
----------------------
              200920



Join our Question Answer community to learn and share your programming knowledge.

Help the community:

Python: Fizzbuzz

C++: Decimal to binary conversion

JavaScript: Need Help in JavaScript

Python: Help me with this program