Analytics-Plus Help

 

MySQL DATE FUNCTIONS

The following table lists all the MySQL in-built Date Time functions supported by ManageEngine Reports. Click on any function to know more about the same.

ADDDATEADDTIMECONVERT_TZCURDATECURRENT_DATECURRENT_TIME
CURTIMEDATEDATEDIFFDAYNAMEDAYOFMONTHDAYOFYEAR
DATE_ADDDATE_SUBEXTRACTFROM_DAYSFROM_UNIXTIMEHOUR
LAST_DAYLOCALTIMESTAMPMAKEDATEMICROSECONDMIDMINUTE
MONTHNOWPERIOD_ADDPERIOD_DIFFQUARTERSEC_TO_TIME
SLEEPSTR_TO_DATESUBDATESUBTIMESYSDATETIME
TIMEDIFFTIMESTAMPTIMESTAMPADDTIMESTAMPDIFFTIME_FORMATTIME_TO_SEC
TO_DAYSUNIX_TIMESTAMPUTC_DATEUTC_TIMESTAMPWEEKWEEKDAY
WEEKOFYEARYEARYEARWEEK

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

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'

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'

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.

CURRENT_DATE():

Purpose :

Its function is the same as CURDATE()

CURRENT_TIME():

Purpose :

Its function is the same as CURTIME()

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.

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.

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.

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.

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.

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.

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

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

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'

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'

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.

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.

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.

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.

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.

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.

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'

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.

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.

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.

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.

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.

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

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.

SLEEP(numeric_arg):

Purpose :

This function is currently not supported by Zoho Reports.

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.

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.

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.

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.

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.

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.

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.

TIMESTAMPADD():

Purpose :

This function is currently not supported by Zoho Reports.

TIMESTAMPDIFF():

Purpose :

This function is currently not supported by Zoho Reports.

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.

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'

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.

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.

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.

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.

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.

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.

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.

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.

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.

Share this post : FacebookTwitter