Skip to Content
CypherFunctionsDATE and TIME functions

DATE and TIME functions

Conversion functions

DATE

Convert an expresion to a date.

Syntx

date(<expression>)

Parameters

Any expression can be converted to DATE type.

Return

A DATE type.

Example

-- convert string to date
date('2023-01-01')
 
-- convert timestamp to date
date(timestamp())
 
-- convert integer to date
date(1672531200)

FROM_ISO8601_DATE

Parses a string in ISO8601 format and returns a date.

Syntax

from_iso8601_date(<string>)

Parameters

  • string: a string in ISO8601 format. yyyy ['-' MM ['-' dd]]

Examples of valid input:

  • '2023'
  • '2023-01'
  • '2023-01-01'

Return

A DATE type.

Example

from_iso8601_date('2023-01-01')

FROM_UNIXTIME

Converts a UNIX timestamp (seconds since the epoch) to a timestamp.

Syntax

from_unix_time(<unixtime>)

Parameters

  • unixtime: a DOUBLE representing the number of seconds since the epoch.

Return

A TIMESTAMP type.

Example

from_unixtime(1672531200.0)

TO_ISO8601

Converts a DATE/TIMESTAMP to a string in ISO8601 format.

Syntax

to_iso8601(<x>)

Parameters

  • x: a DATE/TIMESTAMP.

Return

A VARCHAR.

Example

to_iso8601(date('2023-01-01'))

TO_UNIXTIME

Converts a TIMESTAMP to a UNIX timestamp (seconds since the epoch). Syntax

to_unix_time(<x>)

Parameters

  • x: a TIMESTAMP.

Return

A DOUBLE.

Example

to_unixtime(timestamp())

DATE_FORMAT

Formats a TIMESTAMP to a string.

Syntax

date_format(<x>, <format>)

Parameters

  • x: a TIMESTAMP.
  • format: a VARCHAR. The format to use. Valid values are: 'YYYY-MM-DD', 'YYYY-MM-DD HH:mm:ss', 'YYYY-MM-DD HH:mm:ss.SSS'.

Format specifiers:

SpecifierDescription
%aAbbreviated weekday name (Sun … Sat)
%bAbbreviated month name (Jan … Dec)
%cMonth, numeric (1 … 12) [4]
%DDay of the month with English suffix (0th, 1st, 2nd, 3rd, …)
%dDay of the month, numeric (01 … 31) [4]
%eDay of the month, numeric (1 … 31) [4]
%fFraction of second (6 digits for printing: 000000 … 999000; 1 - 9 digits for parsing: 0 … 999999999) [1]
%HHour (00 … 23)
%hHour (01 … 12)
%IHour (01 … 12)
%iMinutes, numeric (00 … 59)
%jDay of year (001 … 366)
%kHour (0 … 23)
%lHour (1 … 12)
%MMonth name (January … December)
%mMonth, numeric (01 … 12) [4]
%pAM or PM
%rTime, 12-hour (hh:mm:ss followed by AM or PM)
%SSeconds (00 … 59)
%sSeconds (00 … 59)
%TTime, 24-hour (hh:mm:ss)
%UWeek (00 … 53), where Sunday is the first day of the week
%uWeek (00 … 53), where Monday is the first day of the week
%VWeek (01 … 53), where Sunday is the first day of the week; used with %X
%vWeek (01 … 53), where Monday is the first day of the week; used with %x
%WWeekday name (Sunday … Saturday)
%wDay of the week (0 … 6), where Sunday is the first day of the week [3]
%XYear for the week where Sunday is the first day of the week, numeric, four digits; used with %V
%xYear for the week, where Monday is the first day of the week, numeric, four digits; used with %v
%YYear, numeric, four digits
%yYear, numeric (two digits) [2]
%%A literal % character
%xx, for any x not listed above

[1] Timestamp is truncated to milliseconds.

[2] When parsing, two-digit year format assumes range 1970 … 2069, so “70” will result in year 1970 but “69” will produce 2069.

[3] This specifier is not supported yet. Consider using day_of_week() (it uses 1-7 instead of 0-6).

[4] This specifier does not support 0 as a month or day.

Warning:

The following specifiers are not currently supported: %D, %U, %u, %V, %w, %X.

Return

A VARCHAR.

Example

date_format(Cast("2001-08-22 03:04:05.321" as Timestamp), '%Y-%m-%d %H:%i:%s')

Arithmetic functions

DATE_ADD

Adds a specified amount of time to a date/timestamp.

Syntax

date_add(<unit>, <value>, <x>)

Parameters

  • unit: a VARCHAR. The amount of time to add. Valid values are: 'year', 'quarter', 'month', 'week', 'day', 'hour', 'minute', 'second'.
  • value: a BIGINT. The amount of time to add.
  • x: a value of DATE/TIMESTAMP type.

Return

Same type as x. A DATE/TIMESTAMP.

Example

-- add 1 year, returns 2024-01-01
date_add('year', 1, date('2023-01-01'))

DATE_DIFF

Returns the difference between two dates/timestamps in the specified unit.

Syntax

date_diff(<unit>, <x>, <y>)

Parameters

  • unit: a VARCHAR. The unit to return the difference in. Valid values are: 'year', 'quarter', 'month', 'week', 'day', 'hour', 'minute', 'second'.
  • x: a value of DATE/TIMESTAMP type.
  • y: a value of DATE/TIMESTAMP type.

Return

A BIGINT.

Example

-- returns 1
date_diff('year', date('2023-01-01'), date('2024-01-01'))

Convenience Extraction Functions

DAY

Returns the day of the month. BIGINT.

Syntax

day(<x>)

Parameters

  • x: a value of DATE/TIMESTAMP type.

Return

A BIGINT.

Example

-- returns 1
day(date('2023-01-01'))

DAY_OF_WEEK

Returns the day of the week. BIGINT.

Syntax

day_of_week(<x>)

Parameters

  • x: a value of DATE/TIMESTAMP type.

Return

A BIGINT.

Example

-- returns 1
day_of_week(date('2023-01-01'))

DAY_OF_MONTH

Returns the day of the month. This is an alias of DAY. BIGINT.

Syntax

day_of_month(<x>)

Parameters

  • x: a value of DATE/TIMESTAMP type.

Return

A BIGINT.

Example

-- returns 1
day_of_month(date('2023-01-01'))

DAY_OF_YEAR

Returns the day of the year. BIGINT.

Syntax

day_of_year(<x>)

Parameters

  • x: a value of DATE/TIMESTAMP type.

Return

A BIGINT.

Example

-- returns 1
day_of_year(date('2023-01-01'))

DOW

Returns the day of the week. This is an alias of DAY_OF_WEEK. BIGINT.

Syntax

dow(<x>)

DOY

Returns the day of the year. This is an alias of DAY_OF_YEAR. BIGINT.

Syntax

doy(<x>)

LAST_DAY_OF_MONTH

Returns the last day of the month. BIGINT.

Syntax

last_day_of_month(<x>)

Parameters

  • x: a value of DATE/TIMESTAMP type.

Return

A BIGINT.

Example

-- returns 31
last_day_of_month(date('2023-01-01'))

HOUR

Returns the hour of the day. BIGINT.

Syntax

hour(<x>)

Parameters

  • x: a value of DATE/TIMESTAMP type.

Return

A BIGINT.

Example

-- returns 3
hour(timestamp('2023-01-01 03:04:05.321'))

MILLISECOND

Returns the millisecond of the second. BIGINT.

Syntax

millisecond(<x>)

Parameters

  • x: a value of DATE/TIMESTAMP type.

Return

A BIGINT.

Example

-- returns 321
millisecond(timestamp('2023-01-01 03:04:05.321'))

MINUTE

Returns the minute of the hour. BIGINT.

Syntax

minute(<x>)

Parameters

  • x: a value of DATE/TIMESTAMP type.

Return

A BIGINT.

Example

-- returns 4
minute(timestamp('2023-01-01 03:04:05.321'))

MONTH

Returns the month of the year. BIGINT.

Syntax

month(<x>)

Parameters

  • x: a value of DATE/TIMESTAMP type.

Return

A BIGINT.

Example

-- returns 1
month(date('2023-01-01'))

QUARTER

Returns the quarter of the year. BIGINT.

Syntax

quarter(<x>)

Parameters

  • x: a value of DATE/TIMESTAMP type.

Return

A BIGINT.

Example

-- returns 1
quarter(date('2023-01-01'))

SECOND

Returns the second of the minute. BIGINT.

Syntax

second(<x>)

Parameters

  • x: a value of DATE/TIMESTAMP type.

Return

A BIGINT.

Example

-- returns 5
second(timestamp('2023-01-01 03:04:05.321'))

WEEK

Returns the week of the year. BIGINT.

Syntax

week(<x>)

Parameters

  • x: a value of DATE/TIMESTAMP type.

Return

A BIGINT.

Example

-- returns 1
week(date('2023-01-01'))

YEAR

Returns the year. BIGINT.

Syntax

year(<x>)

Parameters

  • x: a value of DATE/TIMESTAMP type.

Return

A BIGINT.

Example

-- returns 2023
year(date('2023-01-01'))

Other functions

CURRENT_DATE

Returns the current date. DATE.

Syntax

current_date()

Return

A DATE.

Example

-- returns 2023-03-04
current_date()

DATE_TRUNC

Truncates a date/timestamp to a specified unit.

Syntax

date_trunc(<unit>, <x>)

Parameters

  • unit: a VARCHAR. The unit to truncate to. Valid values are: 'year', 'quarter', 'month', 'week', 'day', 'hour', 'minute', 'second', 'millisecond'.
  • x: a value of DATE/TIMESTAMP type.

Return

Same type as x. A DATE/TIMESTAMP.

Example

-- truncate to year, returns 2023-01-01
date_trunc('year', date('2023-03-04'))
 
-- truncate to month, returns 2023-03-01
date_trunc('month', date('2023-03-04'))
 
-- truncate to day, returns 2023-03-04
date_trunc('day', date('2023-03-04'))
Last updated on
ARRAY functionsNumeric functions