DateTime related functions are as follows:

Name Description
datediff() difference between the two date-times.
date_format() convert date-time as specified format into CHAR string
day() month day of the date
hour() hour of the date-time
isoweek() iso week number of the date, e.g. 2017W2
minute() minute of the date-time
month() month of the date
now() current date-time.
quarter() 1-4, quarter of the date
second() second of the date-time
time_seg() partition date-time into time segments with the same length of period.
timediff() difference (signed integer) between the two date-times.
timestampdiff() difference (signed integer) between the two date-times.
timestamp() timestamp of the date-time
week() week number of the date.
weekday() 0 (Sunday) - 6 (Saturday), week day of the date.
year() year of the date
yearday() 0-365, day number of the year of the date.
yearweek() week number of the date, e.g. 2017W1


datediff(startdate, enddate)

Function datediff returns the difference in days between startdate and enddate.

datediff(datepart, date1, date2) fucntion works the same as timestampdiff() function.


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


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

Function time_seg converts all date-time within the same time window to the start time of the time window. The length of a time window is indicated by the time window specifier. 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.

The length of a time window is described as a number followed by a time winodow specifier as shown below:

time windows specifier Description
d day
h hour
m minute
s second

For example, "2h30m" represents a time window of two hours and 30 minutes


timestampdiff(datepart, date1, date2)

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

The following lists available datepart types: