SQL-like query language for csv

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
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 returns week 52 or 53 of the last year, and Dec 29 to Dec 31 might returns 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.

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.