Date and Time Functions

Function List

current_timestamp

This function returns the current date and time in the yyyy-MM-dd HH:mm:ss.SSS format.

Syntax: current_timestamp()

Return value type: timestamp

Example: SELECT CURRENT_TIMESTAMP()

from_iso8601_date

This function converts a date expression in the ISO 8601 format to a date expression of the date type in the YYYY-MM-DD format.

Syntax: from_iso8601_date(expr)

Return value type: string

Example: SELECT FROM_ISO8601_DATE('2018-05-14')

from_iso8601_timestamp

This function converts a datetime expression in the ISO 8601 format to a datetime expression of the timestamp type in the YYYY-MM-DD HH:MM:SS.Ms Time_zone format.

Syntax: from_iso8601_timestamp(expr)

Return value type: timestamp

Example: SELECT FROM_ISO8601_TIMESTAMP('2018-05-14T11:51:50.153+08:00')

mills_to_timestamp

This function converts a timestamp in milliseconds to a datetime expression of the timestamp type.

• Converts a timestamp in milliseconds to a datetime expression of the timestamp type in the yyyy-MM-dd HH:mm:ss.SSS format.

  Syntax: mills_to_timestamp(expr)

  Table 7 Parameter description

  Parameter	Description	Type	Mandatory
  expr	UNIX timestamp.	Long	Yes

  Return value type: timestamp

  Example: SELECT MILLS_TO_TIMESTAMP(1626774758000)

  Table 8 Query and analysis results

  Type	Query Statement	Returned Result
  Scenario	MILLS_TO_TIMESTAMP(1626774758000)	2021-07-20 17:52:38.000 Asia/Shanghai

from_unixtime

This function converts a timestamp in seconds or milliseconds to a datetime expression. If the value of expr is less than 315,360,000,000, it is treated as a timestamp in seconds.

• Converts a timestamp in seconds or milliseconds to a datetime expression of the timestamp type.

  Syntax: from_unixtime(expr)

  Table 9 Parameter description

  Parameter	Description	Type	Mandatory
  expr	Timestamp in seconds or milliseconds.	Long	Yes

  Return value type: timestamp

  Example: SELECT FROM_UNIXTIME(1626774758000)

  Table 10 Query and analysis results

  Type	Query Statement	Returned Result
  Scenario	FROM_UNIXTIME(1626774758000)	2021-07-20 17:52:38.000 Asia/shanghai

  Example: SELECT FROM_UNIXTIME(1731916857)

  Table 11 Query and analysis results

  Type	Query Statement	Returned Result
  Scenario	FROM_UNIXTIME(1731916857)	2024-11-18 16:00:57.000 Asia/shanghai

• Converts a timestamp in seconds or milliseconds to a datetime expression of the timestamp type with a specific time zone.

  Syntax: from_unixtime(expr, timezone)

  Table 12 Parameter description

  Parameter	Description	Type	Mandatory
  expr	UNIX timestamp.	Long	Yes
  timezone	Time zone.	String	Yes

  Return value type: string

  Example: SELECT FROM_UNIXTIME(1626774758000, 'Asia/Shanghai')

  Table 13 Query and analysis results

  Type	Query Statement	Returned Result
  Scenario	FROM_UNIXTIME(1626774758000, 'Asia/Shanghai')	2021-07-20 17:52:38.000 Asia/Shanghai

• Converts a timestamp in seconds or milliseconds to a datetime expression with a specific time zone. The hour and minute parameters specify the time zone offset.

  Syntax: from_unixtime(expr, hour, minutes)

  Table 14 Parameter description

  Parameter	Description	Type	Mandatory
  expr	UNIX timestamp.	Long	Yes
  hour	Hour.	Integer	Yes
  minute	Minute.	Integer	Yes

  Return value type: string

  Example: SELECT FROM_UNIXTIME(1626774758000, -2, 0)

  Table 15 Query and analysis results

  Type	Query Statement	Returned Result
  Scenario	FROM_UNIXTIME(1626774758000, -2, 0)	2021-07-20 07:52:38.000 -02:00

to_iso8601

This function converts a date and time expression of the date or timestamp type to ISO 8601 format.

Syntax: to_iso8601(expr)

Return value type: string

Example: SELECT TO_ISO8601(from_unixtime(1626774758000))

timestamp_to_mills

This function converts a datetime expression of the timestamp type to a timestamp in milliseconds.

Syntax: timestamp_to_mills(expr)

Return value type: long

Example: select timestamp_to_mills(from_unixtime(1626774758000))

to_unixtime

This function converts a datetime expression of the timestamp type to a timestamp in milliseconds.

Syntax: to_unixtime(expr)

Return value type: long

Example: SELECT to_unixtime(from_unixtime(1626774758000))

time_ceil

This function rounds up a timestamp of the timestamp or long type to the nearest period and returns the new timestamp. period can be any ISO 8601 duration, such as P3M (three months) or PT12H (12 hours).

time_ceil(expr, period)

Return value type: timestamp

Example 1: SELECT time_ceil(from_unixtime(__time),'PT1M')

Period description: The period format must be P[n]Y[n]M[n]DT[n]H[n]M[n]S or P[n]W. In these representations, [n] indicates the number of date and time elements. Leading zeros are not required, but the maximum number of digits for each element should be agreed to by the communicating parties. The uppercase letters P, Y, M, W, D, T, H, M, and S are designators for each of the date and time elements and are not replaced.

• P is the duration designator (for period) placed at the beginning of the duration representation.
• Y is the year designator that follows the value for the number of calendar years.
• M is the month designator that follows the value for the number of calendar months.
• W is the week designator that follows the value for the number of weeks.
• D is the day designator that follows the value for the number of calendar days.
• T is the time designator that precedes the time components.
• H is the hour designator that follows the value for the number of hours.
• M is the minute designator that follows the value for the number of minutes.
• S is the second designator that follows the value for the number of seconds.

For example, P3Y6M4DT12H30M5S indicates a duration of "3 years, 6 months, 4 days, 12 hours, 30 minutes, and 5 seconds."

For more information, visit https://en.wikipedia.org/wiki/ISO_8601.

time_floor

This function returns a timestamp of the timestamp or long type. period can be any ISO 8601 duration, such as P3M (three months) or PT12H (12 hours).

Syntax: time_floor(expr, period)

Return value type: timestamp

Example 1: SELECT TIME_FLOOR(from_unixtime(1626774758000), 'PT2H')

ceil

This function rounds up a timestamp to the nearest multiple of the specified time unit. The unit can be SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR.

Syntax: ceil(expr, unit)

Return value type: string

Example: SELECT CEIL('2023-04-20 11:28:31.770', 'DAY')

floor

This function rounds down a timestamp to the nearest multiple of the specified time unit. The unit can be SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR.

Syntax: floor(expr, unit)

Return value type: string

Example: SELECT FLOOR('2023-04-20 11:28:31.770', 'DAY')

time_shift

This function moves a timestamp (expr) by a specified period (step time). period can be set to any ISO 8601 duration.

Syntax: time_shift(expr, period, step, [timezone])

Return value type: string

Example: SELECT TIME_SHIFT( from_unixtime(__time), 'P1D', 5), TIME_SHIFT( from_unixtime(__time), 'P1D', 5,'+08:00')

timezone_hour

This function calculates the hour offset of a time zone.

Syntax: timezone_hour(expr)

Return value type: integer

Example: SELECT TIMEZONE_HOUR(from_unixtime(__time))

timezone_minute

This function calculates the minute offset of a time zone.

Syntax: timezone_minute(expr)

Return value type: integer

Example: SELECT TIMEZONE_MINUTE(from_unixtime(__time))

time_format

This function converts a timestamp in milliseconds or a datetime expression of the timestamp type (expr) into a string in a specified date and time format. The conversion is performed based on a user-defined format parameter and follows Joda DateTimeFormat.

Syntax: time_format(expr, format)

Return value type: string

Example: SELECT TIME_FORMAT(__time, 'yyyy-MM-dd HH:mm:ss'), TIME_FORMAT(from_unixtime(__time), 'yyyy-MM-dd HH:mm:ss')

time_parse

This function parses a given string (expr) into a datetime expression of the timestamp type in the yyyy-MM-dd HH:mm:ss.SSS TimeZone format. The parsing is performed based on a user-defined format parameter and follows Joda DateTimeFormat. If format is not specified, the string is parsed according to ISO 8601 by default.

Syntax: time_parse(expr, format)

Return value type: datetime expression of the timestamp type

Example: SELECT TIME_PARSE('2024-11-18 22:15:11', 'yyyy-MM-dd HH:mm:ss')

Description of the format parameter:

Symbol Meaning Presentation Examples

------ ------- ------------ -------

G era text AD

C century of era (>=0) number 20

Y year of era (>=0) year 1996

x weekyear year 1996

w week of weekyear number 27

e day of week number 2

E day of week text Tuesday; Tue

y year year 1996

D day of year number 189

M month of year month July; Jul; 07

d day of month number 10

a halfday of day text PM

K hour of halfday (0~11) number 0

h clockhour of halfday (1~12) number 12

H hour of day (0~23) number 0

k clockhour of day (1~24) number 24

m minute of hour number 30

s second of minute number 55

S fraction of second millis 978

z time zone text Pacific Standard Time; PST

Z time zone offset/id zone -0800; -08:00; America/Los_Angeles

' escape for text delimiter

'' single quote literal '

date_format

This function converts a datetime expression of the timestamp type to a string in the specified date and time format.

Syntax: date_format(expr, format)

Return value type: string

Example: SELECT DATE_FORMAT(current_timestamp(),'%H%i')

Description of the format parameter:

%a Abbreviation for the week. For example, Sun and Sat.

%b Abbreviation of the month. For example, Jan and Dec.

%c Month. Numeral type. Range: 1-12.

%D Day of the month. The value must be suffixed, for example, 0th, 1st, 2nd, and 3rd.

%d Day of the month. The value ranges from 01 to 31 in decimal notation.

%e Day of the month. The value ranges from 1 to 31 in decimal notation.

%H Hour, 24-hour system.

%h Hour, 12-hour system.

%i Minute. Numeral type. Range: 00–59.

%j Day of the year. The value ranges from 001 to 366.

%k Hour. The value ranges from 0 to 23.

%l Hour. The value ranges from 1 to 12.

%M The English expression of the month, for example, January, December.

%m Month. Numeral format. The value ranges from 01 to 12.

%p AM and PM.

%r Time in the 12-hour format. The format is hh:mm:ss AM/PM.

%S Indicates the second. The value ranges from 00 to 59.

%s Indicates the second. The value ranges from 00 to 59.

%T Time, in the 24-hour format of hh:mm:ss.

%v The first week of the year, Monday is the first day of the week. The value ranges from 01 to 53.

%W The name of the day of the week. For example, Sunday and Saturday.

%w Day of the week. Sunday is day 0.

%Y A 4-digit year, for example, 2020.

%y A 2-digit year, for example, 20.

%% Escape character for%.

date_parse

This function converts a date and time string to a datetime expression of the timestamp type in a specified format.

Syntax: date_parse(expr, pattern)

Return value type: datetime expression of the timestamp type.

Example: SELECT DATE_PARSE('2024-11-18 10:53','%Y-%m-%d %h:%i')

If the pattern includes minutes, the year, month, day, and hour components preceding the minutes must also be configured in the pattern.

time_extract

This function extracts a date or time component from a datetime expression by specifying a field.

The unit can be epoch, second, minute, hour, day (a day in a month), DOW (a day in a week), DOY (a day in a year), WEEK (a week in a week), MONTH (1 to 12), QUARTER (1 to 4), or YEAR. If the time zone is provided, it should be the time zone name (for example, America/Los_Angeles) or offset (for example, -08:00).

Syntax: time_extract(expr, unit)

Return value type: long

Example: SELECT TIME_EXTRACT('2023-05-05','YEAR')

date_trunc

This function truncates a datetime expression based on a specified time unit. The time unit can be millisecond, second, minute, hour, day, month, or year.

Syntax: date_trunc(unit, expr)

Return value type: long

Example: SELECT DATE_TRUNC('year', from_unixtime(__time))

date_diff

This function returns the number of units (signed) between timestamps expr1 and expr2.

Syntax: date_diff(unit, expr1, expr2)

Return value type: long

Example: SELECT DATE_DIFF('SECOND', CURRENT_TIMESTAMP() ,from_unixtime(__time))

current_date

Returns the current date in the yyyy-MM-dd format.

Syntax: current_date()

Return value type: string

Example: SELECT CURRENT_DATE()

now

This function returns the current date and time. It is equivalent to the current_timestamp function.

Syntax: now()

Return value type: datetime expression of the timestamp type, in the yyyy-MM-dd HH:mm:ss.SSS TimeZone format.

Example: SELECT NOW()

date_add

This function adds a specified interval to the time.

Syntax: date_add(unit, n, expr)

Return value type: timestamp

Example: SELECT DATE_ADD('MINUTE', 7, from_unixtime(__time))

current_time

This function returns the current time in the HH:mm:ss.SSSSSS format.

Syntax: current_time()

Return value type: string

Example: SELECT CURRENT_TIME()

current_timezone

This function returns the current time zone.

Syntax: current_timezone()

Return value type: string

Example: SELECT CURRENT_TIMEZONE()

localtime

This function returns the local time.

Syntax: localtime()

Return value type: string

Example: SELECT LOCALTIME()

localtimestamp

This function returns the local date and time.

Syntax: localtimestamp

Return value type: string

Example: SELECT LOCALTIMESTAMP

year_of_week

This function returns the year of the target date in the ISO weekly calendar. The year_of_week function is equivalent to the yow function.

Syntax: year_of_week(expr)

Return value type: integer

Example: SELECT YEAR_OF_WEEK('2023-09-22 14:47:59.325'), YEAR_OF_WEEK('2023-09-22')

Return value type: integer

Example: SELECT YEAR_OF_WEEK(from_unixtime(__time))

yow

This function returns the year of the target date in the ISO weekly calendar. The year_of_week function is equivalent to the yow function.

Syntax: yow(expr)

Return value type: integer

Example: SELECT YOW('2023-09-22 14:47:59.325'), YOW('2023-09-22')

Return value type: integer

Example: SELECT YOW(from_unixtime(__time))

time_series

This function completes the missing data in the query time window. It must be used with the GROUP BY and ORDER BY syntax.

Syntax: time_series(x, window_time, format, padding_data)

Return value type: string

Example: select time_series(__time, '1d', 'HH:mm:ss','0') as time, count(*) as count group by time order by time asc