DateTime related functions are as follows:

Name Description
addtime() Add a time interval to a date-time.
datediff() Difference between the two date-times.
date_format() Convert date-time as specified format into CHAR string
day() Month day of a date-time
hour() Hour of a date-time
isoweek() Iso week number of a date-time, e.g. 2017W2
minute() Minute of a date-time
month() Month of a date-time
now() Current date-time.
quarter() 1-4, quarter of a date-time
second() Second of a date-time
str_to_date() Convert string to DATETIME64
str_to_date32() Convert string to DATE32
str_to_datetime32() Convert string to DATETIME32
str_to_datetime64() Convert string to DATETIME64
subtime() Subtract a time interval to a date-time.
time_seg() Partition a date-time into a time segment with the same length of period.
timediff() Difference (signed integer) between two date-times.
timestampdiff() Difference (signed integer) between two date-times.
timestamp() Timestamp of a date-time
week() Week number of a date-time.
weekday() 0 (Sunday) - 6 (Saturday), week day of a day-time
year() Year of a date-time
yearday() 0-365, day number of the year of a date-time
yearweek() Week number of a date-time, e.g. 2017W1

ADDTIME

addtime(date-time, time-interval)

Function addtime returns a new date-time after adding time-interval to date-time.

The time-interval can be either an integer representing seconds or a time window specifier.


DATEDIFF

datediff(startdate, enddate)

Function datediff returns the difference in days between (startdate - enddate).

datediff(datepart, date1, date2) function works the same as timestampdiff() function. Note that it returns the difference specified by the datepart between (date2 - date1).


DATE_FORMAT

date_format(date-time, format)

Function date_format convert a date-time into a CHAR string based on the specified format.

The following format specifiers may be used in the format string.

specifier Description
%a Abbreviated weekday name (Sun..Sat)
%A Full weekday name (Sunday..Saturday)
%b Abbreviated month name (Jan..Dec)
%B Full month name (January..December)
%C Year divided by 100 (00-99)
%d Day of the month (00-31)
%D MM/DD/YY date (08/23/01)
%e Day of the month (0-31)
%F YYYY-MM-DD date (2016-08-23)
%H Hour in 24h format (00-23)
%I Hour in 12h format (01-12)
%m Month (00-12)
%M Minute (00-59)
%p AM or PM
%S Seconds (00-59)
%T HH:MM:SS (14:55:02)
%Y Year (2017)
%% A % character

STR_TO_DATE

str_to_date(string, [date_time_fmt])

str_to_date32(string, [date_time_fmt])

str_to_datetime32(string, [date_time_fmt])

str_to_datetime64(string, [date_time_fmt])

Function str_to_date and related functions converts a string into a corresponding date time based on the format specified by date_time_fmt. If date_time_fmt is omitted, the current DATE_FORMAT or DATE_TIME_FORMAT configuration setting is used.


SUBTIME

subtime(date-time, time-interval)

Function subtime returns a new date-time after subtracting time-interval from date-time.

The time-interval can be either an integer representing seconds or a time window specifier.


TIME_SEG

time_seg(date-time, time_window, [reference_time])

Function time_seg converts all date-times within the same time window to the start time of the time window. The length of a time window is indicated by time window specifiers. The start time for each time window is determined by the reference_time as a start time. If reference_time is not given, "1970/01/01 00:00:00" is used as the default reference time.


TIMESTAMPDIFF

timestampdiff(datepart, date1, date2)

Function timestampdiff returns the difference specified by the datepart between (date2 - date1).

The following lists available datepart types:

datepart
year
quarter
month
week
day
hour
minute
second