Datetime Functions

name	description
NOW	Return a datetime value of current date and time
DATETIME_FORMAT	Format a datetime
YEAR	Return year of a datetime
MONTH	Return month of a datetime
DAY	Return day of a datetime
HOUR	Return hour of a datetime
MINUTE	Return minute of a datetime
SECOND	Return second of a datetime
MILLISECOND	Return millisecond of a datetime
MICROSECOND	Return microsecond of a datetime
NANOSECOND	Return nanosecond of a datetime
WEEKDAY	Return weekday number of a datetime
UNIX_TIME	Return Unix time of a datetime
UNIX_NANO_TIME	Return Unix nano time of a datetime
DAY_OF_YEAR	Return day of year of a datetime
WEEK_OF_YEAR	Return week number of year of a datetime
ADD_YEAR	Add years to a datetime
ADD_MONTH	Add monthes to a datetime
ADD_DAY	Add days to a datetime
ADD_HOUR	Add hours to a datetime
ADD_MINUTE	Add minutes to a datetime
ADD_SECOND	Add seconds to a datetime
ADD_MILLI	Add milliseconds to a datetime
ADD_MICRO	Add microseconds to a datetime
ADD_NANO	Add nanoseconds to a datetime
TRUNC_MONTH	Truncate time information less than 1 year from a datetime
TRUNC_DAY	Truncate time information less than 1 month from a datetime
TRUNC_TIME	Truncate time information less than 1 day from a datetime
TRUNC_HOUR	Alias for TRUNC_TIME
TRUNC_MINUTE	Truncate time information less than 1 hour from a datetime
TRUNC_SECOND	Truncate time information less than 1 minute from a datetime
TRUNC_MILLI	Truncate time information less than 1 second from a datetime
TRUNC_MICRO	Truncate time information less than 1 millisecond from a datetime
TRUNC_NANO	Truncate time information less than 1 microsecond from a datetime
DATE_DIFF	Return the difference of days between two datetime values
TIME_DIFF	Return the difference of time between two datetime values as seconds
TIME_NANO_DIFF	Return the difference of time between two datetime values as nanoseconds
UTC	Return a datetime in UTC
MILLI_TO_DATETIME	Convert an integer representing Unix milliseconds to a datetime
NANO_TO_DATETIME	Convert an integer representing Unix nano time to a datetime

Definitions

NOW

NOW()

return: datetime

Returns a datetime value of current date and time. In a single query, every this function returns the same value.

DATETIME_FORMAT

DATETIME_FORMAT(datetime, format)

datetime: datetime
format: string
return: string

Formats datetime according to format.

Format Placeholders

placeholder	replacement value
%a	Abbreviation of week name (Sun, Mon, …)
%b	Abbreviation of month name (Jan, Feb, …)
%c	Month number (0 - 12)
%d	Day of month in two digits (01 - 31)
%E	Day of month padding with a underscore (_1 - 31)
%e	Day of month (1 - 31)
%F	Microseconds that drops trailing zeros (empty - .999999)
%f	Microseconds (.000000 - .999999)
%H	Hour in 24-hour (00 - 23)
%h	Hour in two digits 12-hour (01 - 12)
%i	Minute in two digits (00 - 59)
%l	Hour in 12-hour (1 - 12)
%M	Month name (January, February, …)
%m	Month number with two digits (01 - 12)
%N	Nanoseconds that drops trailing zeros (empty - .999999999)
%n	Nanoseconds (.000000000 - .999999999)
%p	Period in a day (AM or PM)
%r	Time with a period (%H:%i:%s %p)
%s	Second in two digits (00 - 59)
%T	Time (%H:%i:%s)
%W	Week name (Sunday, Monday, …)
%Y	Year in four digits
%y	Year in two digits
%Z	Time zone in time difference
%z	Abbreviation of Time zone name
%%	’%’

You can also use the Time Layout of the Go Lang as a format.

YEAR

YEAR(datetime)

datetime: datetime
return: integer

Returns the year of datetime as an integer.

MONTH

MONTH(datetime)

datetime: datetime
return: integer

Returns the month number of datetime as an integer.

DAY

DAY(datetime)

datetime: datetime
return: integer

Returns the day of month of datetime as an integer.

HOUR

HOUR(datetime)

datetime: datetime
return: integer

Returns the hour of datetime as an integer.

MINUTE

MINUTE(datetime)

datetime: datetime
return: integer

Returns the minute of datetime as an integer.

SECOND

SECOND(datetime)

datetime: datetime
return: integer

Returns the seconds of datetime as an integer.

MILLISECOND

MILLISECOND(datetime)

datetime: datetime
return: integer

Returns the millisecond of datetime as an integer.

MICROSECOND

MICROSECOND(datetime)

datetime: datetime
return: integer

Returns the microsecond of datetime as an integer.

NANOSECOND

NANOSECOND(datetime)

datetime: datetime
return: integer

Returns the nanosecond of datetime as an integer.

WEEKDAY

WEEKDAY(datetime)

datetime: datetime
return: integer

Returns the weekday number of datetime as an integer.

Weekday number

weekday	number
Sunday	0
Monday	1
Tuesday	2
Wednesday	3
Thursday	4
Friday	5
Saturday	6

UNIX_TIME

UNIX_TIME(datetime)

datetime: datetime
return: integer

Returns the number of seconds elapsed since January 1, 1970 UTC of datetime as an integer.

UNIX_NANO_TIME

UNIX_NANO_TIME(datetime)

datetime: datetime
return: integer

Returns the number of nanoseconds elapsed since January 1, 1970 UTC of datetime as an integer.

DAY_OF_YEAR

DAY_OF_YEAR(datetime)

datetime: datetime
return: integer

Returns the day of the year of datetime as an integer.

WEEK_OF_YEAR

WEEK_OF_YEAR(datetime)

datetime: datetime
return: integer

Returns the week number of the year of datetime as an integer. The week number is in the range from 1 to 53. Jan 01 to Jan 03 of a year might return week 52 or 53 of the last year, and Dec 29 to Dec 31 might return week 1 of the next year.

ADD_YEAR

ADD_YEAR(datetime, duration)

datetime: datetime
duration: integer
return: datetime

Adds duration years to datetime.

ADD_MONTH

ADD_MONTH(datetime, duration)

datetime: datetime
duration: integer
return: datetime

Adds duration monthes to datetime.

ADD_DAY

ADD_DAY(datetime, duration)

datetime: datetime
duration: integer
return: datetime

Adds duration days to datetime.

ADD_HOUR

ADD_HOUR(datetime, duration)

datetime: datetime
duration: integer
return: datetime

Adds duration hours to datetime.

ADD_MINUTE

ADD_MINUTE(datetime, duration)

datetime: datetime
duration: integer
return: datetime

Adds duration minutes to datetime.

ADD_SECOND

ADD_SECOND(datetime, duration)

datetime: datetime
duration: integer
return: datetime

Adds duration seconds to datetime.

ADD_MILLI

ADD_MILLI(datetime, duration)

datetime: datetime
duration: integer
return: datetime

Adds duration milliseconds to datetime.

ADD_MICRO

ADD_MICRO(datetime, duration)

datetime: datetime
duration: integer
return: datetime

Adds duration microseconds to datetime.

ADD_NANO

ADD_NANO(datetime, duration)

datetime: datetime
duration: integer
return: datetime

Adds duration nanoseconds to datetime.

TRUNC_MONTH

TRUNC_MONTH(datetime)

datetime: datetime
return: datetime

Truncates time information less than 1 year from datetime.

TRUNC_DAY

TRUNC_DAY(datetime)

datetime: datetime
return: datetime

Truncates time information less than 1 month from datetime.

TRUNC_TIME

TRUNC_TIME(datetime)

datetime: datetime
return: datetime

Truncates time information less than 1 day from datetime.

TRUNC_MINUTE

TRUNC_MINUTE(datetime)

datetime: datetime
return: datetime

Truncates time information less than 1 hour from datetime.

TRUNC_SECOND

TRUNC_SECOND(datetime)

datetime: datetime
return: datetime

Truncates time information less than 1 minute from datetime.

TRUNC_MILLI

TRUNC_MILLI(datetime)

datetime: datetime
return: datetime

Truncates time information less than 1 second from datetime.

TRUNC_MICRO

TRUNC_MICRO(datetime)

datetime: datetime
return: datetime

Truncates time information less than 1 millisecond from datetime.

TRUNC_NANO

TRUNC_NANO(datetime)

datetime: datetime
return: datetime

Truncates time information less than 1 microsecond from datetime.

DATE_DIFF

DATE_DIFF(datetime1, datetime2)

datetime1: datetime
duration2: datetime
return: integer

Returns the difference of days between two datetime values. The time information less than 1 day are ignored in the calculation.

TIME_DIFF

TIME_DIFF(datetime1, datetime2)

datetime1: datetime
duration2: datetime
return: float or integer

Returns the difference of time between two datetime values as seconds. In the return value, the integer part represents seconds and the fractional part represents nanoseconds.

TIME_NANO_DIFF

TIME_NANO_DIFF(datetime1, datetime2)

datetime1: datetime
duration2: datetime
return: integer

Returns the difference of time between two datetime values as nanoseconds.

UTC

UTC(datetime)

datetime: datetime
return: datetime

Returns the datetime value of datetime in UTC.

MILLI_TO_DATETIME

MILLI_TO_DATETIME(unix_milliseconds)

unix_milliseconds: integer
return: datetime

Converts an integer representing Unix milliseconds to a datetime.

NANO_TO_DATETIME

NANO_TO_DATETIME(unix_nano_time)

unix_nano_time: integer
return: datetime

Converts an integer representing Unix nano time to a datetime.