MySQL Date and Time Functions, slides presentation
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 CURDATE();
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
It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.
https://w3resource.com/slides/mysql-date-and-time-functions-slides-presentation.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics