Home  »   Zoho Reports CloudSQL »  Supported SQL »  MySQL Date Time Functions

MySQL Date Time Functions

Tags:  

MySQL DATE FUNCTIONS

The following table lists all the MySQL in-built Date Time functions supported by Zoho Reports. Click on any function to know more about the same.
ADDDATE ADDTIME CONVERT_TZ CURDATE CURRENT_DATE CURRENT_TIME
CURTIME DATE DATEDIFF DAYNAME DAYOFMONTH DAYOFYEAR
DATE_ADD DATE_SUB EXTRACT FROM_DAYS FROM_UNIXTIME HOUR
LAST_DAY LOCALTIMESTAMP MAKEDATE MICROSECOND MID MINUTE
MONTH NOW PERIOD_ADD PERIOD_DIFF QUARTER SEC_TO_TIME
SLEEP STR_TO_DATE SUBDATE SUBTIME SYSDATE TIME
TIMEDIFF TIMESTAMP TIMESTAMPADD TIMESTAMPDIFF TIME_FORMAT TIME_TO_SEC
TO_DAYS UNIX_TIMESTAMP UTC_DATE UTC_TIMESTAMP WEEK WEEKDAY
WEEKOFYEAR YEAR YEARWEEK

Function List ADDDATE(date_arg, numeric_arg):
Purpose:
The first argument is a date value and the second argument is the number of days to be added to it. Returns the new date.
Example:
Select ADDDATE ( ' 2008-08-03 ' , 20 )
Note:
  • The interval to be added should be in numeric value only and should not be in the form of 'INTERVAL 31 DAYS'.
Function List ADDTIME(time_arg, time_arg):
Purpose:
The addition value of first argument time and second argument time is returned.
Example:
Select ADDTIME ('12:30:15.55555555', '01:00:44.444445') returns '13:31:00.000000'
     
Function List CONVERT_TZ(datetime_arg, time_arg, time_arg):
Purpose:
Converts the given time value to the required time value by adding the time given in the third argument.The resultant time value comes in 12 hour or 24 hour format depending on the second argument.
Example:
Select CONVERT_TZ ( ' 2008-11-06 03:00:00 ' , ' +09:00 ' , ' -5:30 ' ) returns ' 2008-11-05 12:30:00 '
Note:
  • For the result to be in 12 hour format, the second argument should be '12:00'
  • For the result to be in 24 hour format, the second argument should be '00:00'
Function List CURDATE():
Purpose:
The present date is returned in the format of ' yr:mth:dt '.
Example:
Select CURDATE () returns '2008-11-06'
Note:
  • Depending on the way the function is used the result is returned as a string or a number.
Function List CURRENT_DATE():
Purpose:
Its function is the same as CURDATE()
     
Function List CURRENT_TIME():
Purpose:
Its function is the same as CURTIME()
     
Function List CURTIME():
Purpose:
Returns the present time of the system in the form of ' hr : min : sec '.
Example:
Select CURTIME() returns ' 12 : 46 : 21 '
Select CURTIME()+0 returns ' 124621.0 '
Note:
  • Depending on its usage the function returns the result as a string or a number.
Function List DATE(date.time_arg):
Purpose:
Returns the date part alone from the date-time argument given in the function.
Example:
Select DATE ( ' 2008-08-03 03:45:00 ' ) returns ' 2008-08-03 '
Note:
  • Returns null if the date part is non-numeric.
  • Returns the date part correctly even if the time part is non-numeric.
Function List DATEDIFF(date.time_arg, date.time_arg):
Purpose:
Returns the difference between the 2 dates given in the arguments. For subtraction only the date part of the date-time values of both the argument is taken.
Example:
Select DATEDIFF ( '1986-08-03 03:45:22','1986-08-23 18:45:43') returns '-20'
Note:
  • The time part is not taken in the calculation. Hence even if the time part is non-numeric no difference to the result takes place.
Function List DAYNAME(date_arg):
Purpose:
Returns the day of the week the given date is .
Example:
Select DAYNAME ( ' 2008-11-03 ' ) returns ' Monday '
Note:
  • If the given date's month and year are wrong then null is returned.
  • If the given date's value exceeds 31 then null is returned.
  • The day of a month is returned even if the given date is wrong with respect to the given month. for eg: 31st february 2008 returns 'Sunday'.
  • If the given date's value is '0' then '0' is returned.
  • This function works for the years ranging from 0 to 9999.
Function List DAYOFMONTH(date_arg):
Purpose:
Returns the day of the month the date given in the argument is.
Example:
Select DAYOFMONTH ( ' 2008-11-03 ' ) returns '3'
Note:
  • If the given date's month and year are wrong then null is returned.
  • If the given date's day value exceeds 31 then null is returned.
  • The date of a month is returned even if the given date is wrong with respect to the given month. for eg: 31st february returns 31.
  • If the given date's value is '0' then '0' is returned.
  • This function works for the years ranging from 0 to 9999.
Function List DAYOFYEAR(date_arg):
Purpose:
Returns the given date's count from the start of the year.
Example:
Select DAYOFYEAR ( ' 2008-12-25 ' ) returns '360'
Note:
  • Returns null if the given date and month are '0' .
  • The range of the result is between 1 and 366.
Function List DATE_ADD(date_arg, numeric_arg):
Purpose:
Returns the given date after performing the required arithmetic on it. DATE_ADD is currently not supported by Zoho Reports.
     
Function List DATE_SUB(date_arg, numeric_arg):
Purpose:
Returns the given date after performing the required arithmetic on it. DATE_SUB is currently not supported by Zoho Reports.
     
Function List EXTRACT(command from date.time_arg):
Purpose:
Returns the required part of the date-time value after extraction from the given date-time argument.
Example:
Select EXTRACT ( HOUR_SECOND FROM ' 2009-07-02 01:02:03 ' ) returns ' 10203 '
     
Function List FROM_DAYS(numeric_arg):
Purpose:
The count of days is started from the date 01-01-01. The argument value is taken as count and its corresponding date is displayed.
Example:
Select FROM_DAYS ( 733714) returns ' 2008-11-03 '
Note:
  • The count starts from the number 366 which is the count for the date '0001-01-01'
Function List FROM_UNIXTIME(numerical_arg):
Purpose:
For the given argument the value of the internal timestamp is returned.
Example:
Select FROM_UNIXTIME (1225741235) returns ' 2008-11-03 19:40:35 '
Note:
  • Returns the result in number value or string value depending on the type of the given input argument.
Function List HOUR(time_arg):
Purpose:
Returns the number of hours present in the given time value. The time is given in the form 'hr : min : sec'
Example:
Select HOUR (' 220:22:34 ') returns '220'
Note:
  • Returns null if the value of minutes or seconds exceed '59'
  • If the time is given in decimal form then '0' is returned.
Function List LAST_DAY(date.time_arg):
Purpose:
Returns the last date of the given date-time argument's month.
Example:
Select LAST_DAY ( ' 2004-02-31 01:02:03 ' ) returns '29'
Note:
  • Returns null if the given day, month or year is wrong or exceeds their range.
Function List LOCALTIMESTAMP():
Purpose:
The present date-time values of the application's time zone is returned. The result is returned in the form of ' yr : mth : dt hr : min : sec '.
Example:
Select LOCALTIMESTAMP () returns '2008-08-23 12:59:41'
Note:
  • The result is returned in numeric form or string according to the function's use in the query.
Function List MAKEDATE(numeric_arg, numeric_arg):
Purpose:
The argument contains the year and the count of the day for that year. The date is returned.
Example:
Select MAKEDATE ( 2008,215 ) returns ' 2008-08-03 '
Note:
  • Returns null if the count passed is '0'.
  • If the count is greater than 366, then the year of the date also changes.
Function List MICROSECOND(date.time_arg):
Purpose:
From the given date-time argument value, the microsecond term alone is returned.
Example:
Select MICROSECOND ( ' 2008-11-04 17:16:50.123 ' ) returns '123000'
Note:
  • The default number of microsecond digits that is returned is '6'
  • Returns '0' if there is no microsecond term in the argument.
Function List MID(date_arg, numeric_arg):
Purpose:
If the given date of a new year is in the middle of the week of the previous year then the count of the last week of the previous year is returned.
Example:
Select MID ( YEARWEEK ( ' 2001-01-06 ' ),5 ) returns ' 53 '
     
Function List MINUTE(time_arg):
Purpose:
Returns the number of minutes present in the given time value. The time is given in the form 'hr : min : sec'
Example:
Select MINUTE (' 220:22:34 ') returns '22'
Note:
  • Returns null if the value of minutes or seconds exceed '59'
  • If the time is given in decimal form then '0' is returned.
Function List MONTH(date_arg):
Purpose:
This function returns the month of the given date.
Example:
Select MONTH ( ' 2008-08-03 ' ) returns '8'
Note:
  • Returns '0' if the given date contains '00' months.
  • The range of the year should be between 0 to 9999.
Function List NOW():
Purpose:
The present date-time values of the application's time zone is returned. The result is returned in the form of ' yr : mth : dt hr : min : sec '. Its function is same as LOCALTIMESTAMP()
Example:
Select NOW () returns '2008-08-23 12:59:41'
Note:
  • The result is returned in numeric form or string according to the function's use in the query.
Function List PERIOD_ADD(numeric_arg, numeric_arg):
Purpose:
The argument contains the date and the number of months to be added to it. The date is passed in the format of period i.e 'yrmth'.
Example:
Select PERIOD_ADD ( 198608,06 ) returns ' 198702 '
Note:
  • The date that is passed or returned is not a date value, but is a period value.
  • If the date is passed in the usual form i.e in the format of 'yr:mth:dt' then the result value that is returned is wrong.
Function List PERIOD_DIFF(numeric_arg, numeric_arg):
Purpose:
2 dates in the form of period values are passed in the arguments. The number of months present between the 2 values is returned.
Example:
Select PERIOD_DIFF ( 198608, 198606 ) returns '-10'
Note:
  • Both the arguments should be in date-period form.
  • Returns the result in positive if the second period is chronologically first.
  • Returns the result in negative if the second period is chronologically second.
Function List QUARTER(date_arg):
Purpose:
For the given date, the corresponding quarter of year is returned, ie returns '1' if the months are 1,2,3; returns 2 if the months are 4,5,6; returns 3 if the months are 7,8 9 and returns 4 if the months are 10,11,12.
Example:
Select QUARTER ( ' 2008-08-23 ' ) returns '3'
Note:
  • Returns '0' if the given month is '0'.
  • Returns null if the given date's year, month or day is out of range.
  • Returns the corresponding month's quarter even if the year or day or both are '0'.
Function List SEC_TO_TIME(numeric_arg):
Purpose:
The argument passed is the count of seconds of a day. It's value in hours, minutes and seconds are returned.
Example:
Select SEC_TO_TIME ( 86399 ) returns ' 23:59:59 '
Note:
  • At '86400' the value of 'hr:min:sec' are resetted to '0' and the cycle starts again.
  • As soon as a non-numeric character is found in the string then the SEC_TO_TIME of the numbers before the character is returned.
  • Depending on the given argument format the result is given in the form of number or string.
Function List SLEEP(numeric_arg):
Purpose:
This function is currently not supported by Zoho Reports.
     
Function List STR_TO_DATE(numeric_arg, string_arg):
Purpose:
Returns the date-time value after its conversion from the given string. The conversion is done after taking note of the format of the given string . The format is given as second argument.
Example:
Select STR_TO_DATE ( ' 06/31/2004 ', ' %m/%d/%Y ' ) returns ' 2004-07-01 '
Note:
  • Returns null if the date, month or year exceeds their respective range.
  • Returns the date or time value depending on the respective presence in the string.
Function List SUBDATE(date.time_arg, numeric_arg):
Purpose:
Returns the date-time value after subtracting the number of days passed in the second argument from the date-time value passed in the first argument.
Example:
Select SUBDATE ( ' 2008-02-19 12:00:00 ', 31 ) returns ' 2008 -01-19 12:00:00 '
Select SUBDATE ( ' 2008-02-19 ', INTERVAL 31 DAY ) // This query is currently not supported by Zoho Reports
Note:
  • The range of years for which the function works correctly is '200-9999'. Returns wrong value if year is below '200' and returns null if it is above '9999'
  • Returns null if the month is a number other than 1-12.
  • Returns null if the day is a number other than 1-31.
Function List SUBTIME(date.time_arg, time_arg):
Purpose:
The first argument is a date-time value and the second argument is a time value. The subtraction value of second argument from the first argument is returned.
Example:
Select SUBTIME ( ' 1986-08-03 18:45:00 ' , ' 03:45:00 ' ) returns ' 1986-08-03 15:00:00 '
Note:
  • Returns null if the date value is passed in the second argument.
  • The range of years for which the function works correctly is '200-9999'. Returns wrong value if year is below '200' and returns null if it is above '9999'.
  • Returns null if the month is a number other than 1-12.
  • Returns null if the day is a number other than 1-31.
Function List SYSDATE():
Purpose:
The present date-time value of the application's time zone is returned in the form of ' yr : mth : dt hr : min : sec '.
Example:
Select SYSDATE () returns ' 2008-11-05 05:41:16 '
Note:
  • The result is returned in numeric form or string form according to the function's use in the query.
Function List TIME(date.time_arg):
Purpose:
From the given date-time value argument, the time value is returned.
Example:
Select TIME ( ' 2008-11-05 17:34:45 ' ) returns ' 17:34:45 '
Note:
  • If the date value alone is passed, then the function assumes the year term as the time and returns it.
  • Returns null if the date value is wrong.
Function List TIMEDIFF(time_arg, time_arg):
Purpose:
Returns the difference between the 2 time values passed in the 2 arguments.
Example:
Select TIMEDIFF('20:08:05','20:07:34') returns '00:00:31'
Note:
  • The first argument should be greater than the second argument or else the function will not work.
  • If date value is passed in the any one argument null is returned.
  • If date value is passed in both the arguments then the function will not work.
Function List TIMESTAMP(date.time_arg, time_arg):
Purpose:
The first argument contains both the date and time values. The second argument should contain only the time values. Both the arguments are added and the resultant date-time value is returned.
Example:
Select TIMESTAMP ( ' 2008-11-05 19:00:00 ' , ' 06:00:00 ' ) returns ' 2008-11-06 01:00:00 '
Note:
  • Returns the date time values of the first argument alone if only the first argument is passed.
  • Returns null if the second argument contains date values.
Function List TIMESTAMPADD():
Purpose:
This function is currently not supported by Zoho Reports.
     
Function List TIMESTAMPDIFF():
Purpose:
This function is currently not supported by Zoho Reports.
     
Function List TIME_FORMAT(time_arg, string_arg):
Purpose:
The first argument is the time and the second argument is the format string containing format specifiers. The formatted value is returned.
Example:
Select TIME_FORMAT ( ' 19:30:41.32 ' , ' %k %l %i %s %f ' ) returns ' 19 7 30 41 320000 '
Note:
  • The format string must contain only the time format specifiers.
  • Even if a single non-time specifier is passed then only null value is returned.
Function List TIME_TO_SEC(time_arg):
Purpose:
For the time value passed the total number of seconds is returned after converting the minutes and hours to seconds.
Example:
Select TIME_TO_SEC ( ' 01:00:00 ' ) returns ' 3600 '
     
Function List TO_DAYS(date_arg):
Purpose:
Converts the passed date argument to the total number of days passed since the date '0001-01-01'
Example:
Select TO_DAYS ( ' 2008-11-07 ' ) returns ' 733718 '
Note:
  • The range of years for which the function works correctly is '200-9999'. Returns wrong value if year is below '200' and returns null if it is above '9999'.
  • Returns null if the month is a number other than 1-12.
  • Returns null if the day is a number other than 1-31.
Function List UNIX_TIMESTAMP(date.time_arg):
Purpose:
Returns the number of seconds completed since the date ' 1970-01-01 00:00:00 '
Example:
Select UNIX_TIMESTAMP ( ' 1970-01-01 00:30:00 ' ) returns ' 1800 '
Note:
  • Returns '0' if a date earlier than the date ' 1970-01-01 00:00:00 ' is passed.
Function List UTC_DATE():
Purpose:
The present UTC date is returned.
Example:
Select UTC_TIMESTAMP () returns ' 2008-11-06 05:40:58 '
Note:
  • Returns the current datetime value as a number or a string value depending on the format of function used.
Function List UTC_TIMESTAMP():
Purpose:
The present UTC date-time value is returned.
Example:
Select UTC_TIMESTAMP () returns ' 2008-11-06 05:40:58 '
Note:
  • Returns the current datetime value as a number or a string value depending on the format of function used.
Function List WEEK(date_arg):
Purpose:
Returns the week of the year in which the given date is present.
Example:
Select WEEK(' 2008-01-14 ') returns '2'
Note:
  • By default the week is assumed to be starting on Sunday.
  • If we want the week to start from Monday then we should pass a MODE in the second argument.
Function List WEEKDAY(date_arg):
Purpose:
Returns the day of the week the given date is. Returns '0' if its a Monday, '1' if its Tuesday,......'6' if it is Sunday.
Example:
Select WEEKDAY ( ' 2008-11-06 ' ) returns '3' since its a thursday.
Note:
  • The given argument can be a date-time value or only a date value.
Function List WEEKOFYEAR(date_arg):
Purpose:
Returns the week of the year in which the given date is present.
Example:
Select WEEKOFYEAR ( ' 2008-01-14 ' ) returns '2'
Note:
  • Unlike WEEK(), in this function the second argument cannot be used.
Function List YEAR(date_arg):
Purpose:
This function returns the year of the given date.
Example:
Select YEAR ( ' 2008-11-22 ' ) returns ' 2008 '
Note:
  • The range of the year should be between 0 to 9999.
  • Returns null if the given date, month or year is wrong.
Function List YEARWEEK(date_arg):
Purpose:
Starting the count of the first week from the date '0000-01-01' the week of the given date is returned.
Example:
Select YEARWEEK ( ' 2000-01-02 ' ) returns ' 200001 '
Note:
  • Returns null if the month is a number other than 1-12.
  • Returns null if the day is a number other than 1-31.
  • Returns null if the year is a number above 9999.

Post a comment

Your Name or E-mail ID (mandatory)

Note: Your comment will be published after approval of the owner.





 RSS of this page