Date/Time Functions

CLOCK_TIMESTAMP()

Returns the date & time as YYYY-MM-DD HH24:MI:SS.mmm

CLOCK_TIMESTAMP may return different values each time it is called in the same query or SQL Procedure, which may lead to data getting out of sync across HA clusters. Use CURRENT_DATETIME to avoid this issue.

CURRENT_DATE()

Returns the date as YYYY-MM-DD

CURRENT_DATETIME()

Returns the date & time as YYYY-MM-DD HH24:MI:SS.mmm

CURRENT_DATETIME will return same values each time it is called in the same query or SQL Procedure, and should keep data in-sync across HA clusters. See CLOCK_TIMESTAMP to always get the actual time each time it is called.

CURRENT_TIME()

Returns the time as HH24:MI:SS.mmm

CURRENT_TIMESTAMP()

Returns the date & time as YYYY-MM-DD HH24:MI:SS.mmm; to return the date & time as the number of milliseconds since the epoch, pass the result of this function to LONG()

DATEADD (unit, amount, expr)

Adds the positive or negative integral amount of unit date/time intervals to the date/time in exprThe following date/time intervals are supported for unit:

Constant	Description
`YEAR`	Year is modified by interval amount (not affected by leap year, etc.)
`QUARTER`	Month is modified by three times the interval amount, irrespective of the number of days in the months between; day adjusting performed the same as the `MONTH` description, but only on final month (e.g., Jan 31st + 1 quarter will be Apr 30th, not Apr 28th because of February)
`MONTH`	Month is modified by interval amount and date adjusted if overflow/underflow occurs; day adjusted to last day of calculated month if not a valid day for that month (e.g. Apr 31st -> Apr 30th)
`WEEK`	Day is modified by 7 times the interval amount (time not affected by daylight savings time, etc.); month & year are adjusted, if overflow/underflow occurs
`DAY`	Day is modified by interval amount (time not affected by daylight savings time, etc.); date is adjusted, if overflow/underflow occurs
`HOUR`	Hour is modified by interval amount (time not affected by daylight savings time, etc.); date is adjusted, if overflow/underflow occurs
`MINUTE`	Minute is modified by interval amount; date/time are adjusted, if overflow/underflow occurs
`SECOND`	Second is modified by interval amount; date/time are adjusted, if overflow/underflow occurs
`MILLISECOND`	Millisecond is modified by interval amount; date/time are adjusted, if overflow/underflow occurs
`FRAC_SECOND`	Nanosecond is modified by interval amount; date/time are adjusted, if overflow/underflow occurs Time is processed to millisecond precision, so any portion of an `amount` with finer granularity than 1,000,000 nanoseconds will be ignored (e.g., requesting the addition of 1,234,567 nanoseconds will result in 1 millisecond actually being added)

Any of these unit types can have a SQL_TSI_ prefix prepended to them; e.g., both DAY and SQL_TSI_DAY are valid unit types for specifying a day interval.

Examples:

Function Call	Result
`DATEADD(YEAR, 1, ‘2000-10-10’)`	`2001-10-10`
`DATEADD(QUARTER, 1, ‘2000-11-30’)`	`2001-02-28`
`DATEADD(MONTH, 1, ‘2000-01-31’)`	`2000-02-29`
`DATEADD(WEEK, 53, ‘2000-01-01’)`	`2001-01-06`
`DATEADD(DAY, 1, ‘2000-12-31’)`	`2001-01-01`
`DATEADD(HOUR, 12, ‘2000-10-10 12:34:56’)`	`2000-10-11 00:34:56.000`
`DATEADD(MINUTE, 1, ‘2000-10-10 12:34:56’)`	`2000-10-10 12:35:56.000`
`DATEADD(SECOND, 1, ‘2000-12-31 23:59:59’)`	`2001-01-01 00:00:00.000`
`DATEADD(MILLISECOND, 1, ‘2000-10-10 12:34:56’)`	`2000-10-10 12:34:56.001`
`DATEADD(FRAC_SECOND, 1000000, ‘2000-10-10 12:34:56’)`	`2000-10-10 12:34:56.001`
`DATEADD(SECOND, 1, TIME ‘12:34:56’)`	`12:34:57.000`

DATE_BUCKET (width, ds[, offset[, base]])

Calculates the date range in which a given date ds falls, based on a set of fixed-width “buckets” with the given width, start-aligned base date, and offset from that base date.The width is the number of days each bucket should span.The offset is the number of days after (positive offset) or number of days before (negative offset) the base date to which the buckets should be aligned. The default is no offset.The default base is 2000-01-03.Typically, DATE_BUCKET is used in the following type of query:

SELECT
    DATE_BUCKET(7, ds, -3, '2023-02-21')
        + INTERVAL 3 DAYS AS week_midpoint,
    AVG(cpu) AS avg_cpu
FROM example.host_metrics_summary
GROUP BY week_midpoint
ORDER BY week_midpoint

The result will be as follows:

Dates in the ds column of the example.host_metrics_summary table will be grouped into buckets
Each bucket will span a range of 7 days
The baseline bucket will start at 2023-02-18 (2023-02-21 offset by -3 days) and continue through 2023-02-24 (7 days, including 2023-02-18)
Buckets will extend before & after the baseline bucket in contiguous, non-overlapping fashion
Each result record will show the date in the middle of the bucket’s date range (+ INTERVAL 3 DAYS from the start of each 7 day span) and the average CPU usage across the records contained within that date range
Gaps in the data will not be filled in with empty buckets—only buckets containing the dates found in the ds column of example.host_metrics_summary will be returned in the result set

DATEDIFF ([unit,] begin, end)

Calculates the difference between two date/time expressions, returning the result as an integral difference in the units specified; more precisely, how many whole date/time intervals of type unit need to be added to (or subtracted from) begin to equal end (or get as close as possible without going past it) using the unit types and and rules specified in DATEADD.The default unit is DAY.

This is not symmetric with DATEADD in all cases, as adding 1 MONTH to Mar 31st results in Apr 30th, but the DATEDIFF in MONTH units between those two dates is 0.

Examples:

Function Call	Result
`DATEDIFF(DATE(‘2000-10-10’), DATE(‘2000-12-31’))`	`82`
`DATEDIFF(DATE(‘2000-03-31’), DATE(‘2000-04-30’))`	`30`
`DATEDIFF(DATE(‘2000-12-31’), DATE(‘2000-10-10’))`	`-82`
`DATEDIFF(DATETIME(‘2000-10-10 12:34:56’), 978222896000)`	`81`
`DATEDIFF(MONTH, DATE(‘2000-10-10’), DATE(‘2000-12-31’))`	`2`
`DATEDIFF(MONTH, DATE(‘2000-03-31’), DATE(‘2000-04-30’))`	`0`
`DATEDIFF(MONTH, DATE(‘2000-12-31’), DATE(‘2000-10-10’))`	`-2`
`DATEDIFF(HOUR, DATETIME(‘2000-10-10 12:34:56’), 978222896000)`	`1956`

DATEPART(part, expr)

Returns the requested part of the date/time expr. The part may be any of those specified in DATE_TRUNC(part, expr), plus the following:

Constant	Description
`SECS_SINCE_EPOCH`	Return the number of seconds since the epoch (1970-01-01).
`MSECS_SINCE_EPOCH`	Return the number of milliseconds since the epoch.

Examples:

Function Call	Result
`DATEPART(YEAR, ‘2000-12-31’)`	`2000`
`DATEPART(MONTH, ‘2000-03-31 12:34:56.000’)`	`3`
`DATEPART(DAY, ‘2000-03-31 12:34:56.000’)`	`31`
`DATEPART(SECOND, ‘2000-03-31 12:34:56.220’)`	`56`
`DATEPART(MSECS_SINCE_EPOCH, ‘2000-03-31 12:34:56.220’)`	`954506096220`

DATE_TRUNC(part, expr)

Returns the date/time expr after truncating it beyond the given date/time part. The following date/time constants are supported for part:

Constant	Description
`YEAR`	Return the first day of the year in which `expr` occurs
`QUARTER`	Return the first day of the quarter in which `expr` occurs
`MONTH`	Return the first day of the month in which `expr` occurs
`WEEK`	Return the first day of the week in which `expr` occurs
`DAY`	Return the date (at midnight) on which `expr` occurs
`HOUR`	Return the timestamp up to the hour in which `expr` occurs
`MINUTE`	Return the timestamp up to the minute in which `expr` occurs
`SECOND`	Return the timestamp up to the second in which `expr` occurs
`MILLISECOND`	Return the timestamp up to the millisecond in which `expr` occurs

Examples:

Function Call	Result
`DATE_TRUNC(YEAR, ‘2008-09-10 12:34:56.789’)`	`2008-01-01 00:00:00.000`
`DATE_TRUNC(QUARTER, ‘2008-09-10 12:34:56.789’)`	`2008-07-01 00:00:00.000`
`DATE_TRUNC(MONTH, ‘2008-09-10 12:34:56.789’)`	`2008-09-01 00:00:00.000`
`DATE_TRUNC(WEEK, ‘2008-09-10 12:34:56.789’)`	`2008-09-07 00:00:00.000`
`DATE_TRUNC(DAY, ‘2008-09-10 12:34:56.789’)`	`2008-09-10 00:00:00.000`
`DATE_TRUNC(HOUR, ‘2008-09-10 12:34:56.789’)`	`2008-09-10 12:00:00.000`
`DATE_TRUNC(MINUTE, ‘2008-09-10 12:34:56.789’)`	`2008-09-10 12:34:00.000`
`DATE_TRUNC(SECOND, ‘2008-09-10 12:34:56.789’)`	`2008-09-10 12:34:56.000`
`DATE_TRUNC(MILLISECOND, ‘2008-09-10 12:34:56.789’)`	`2008-09-10 12:34:56.789`

DAY(expr)

Alias for DAYOFMONTH(expr)

DAYNAME(expr)

Extracts the day of the week from expr and converts it to the corresponding day name [Sunday - Saturday ]

DAYOFMONTH(expr)

Extracts the day of the month from expr [1 - 31]

DAYOFWEEK(expr)

Extracts the day of the week from expr [1 - 7]

Expression Value	Result
Date on Sunday	`1`
Date on Monday	`2`
Date on Tuesday	`3`
Date on Wednesday	`4`
Date on Thursday	`5`
Date on Friday	`6`
Date on Saturday	`7`

DAY_OF_WEEK(expr)

Alias for DAYOFWEEK(expr)

DAYOFYEAR(expr)

Extracts the day of the year from expr [1 - 366]

DAY_OF_YEAR(expr)

Alias for DAYOFYEAR(expr)

EPOCH_MSECS_TO_DATETIME(expr)

Converts expr milliseconds since the epoch to a date/timeExample:

Function Call	`EPOCH_MSECS_TO_DATETIME(971181296789)`
Return	`2000-10-10 12:34:56.789`

EPOCH_SECS_TO_DATETIME(expr)

Converts expr seconds since the epoch to a date/timeExample:

Function Call	`EPOCH_SECS_TO_DATETIME(971181296)`
Return	`2000-10-10 12:34:56.000`

EXTRACT(<part> FROM <expr>)

Extracts the date/time part from the date/time expr. This function is used to support database clients which require the call in this form; however, each of the supported date/time part constants results in a call for which there is a simpler, more direct corresponding call, which are listed below.

Constant	Corresponding Alternative Function Call
`YEAR`	`YEAR(expr)`
`QUARTER`	`QUARTER(expr)`
`MONTH`	`MONTH(expr)`
`WEEK`	`WEEK(expr)`
`DAY`	`DAY(expr)`
`HOUR`	`HOUR(expr)`
`MINUTE`	`MINUTE(expr)`
`SECOND`	`SECOND(expr)`
`MILLISECOND`	`MSEC(expr)`
`EPOCH`	`SECS_SINCE_EPOCH(expr)`

HOUR(expr)

Extracts the hour of the day from expr [0 - 23]

<expr> + INTERVAL '<amount>' <part> <expr> - INTERVAL '<amount>' <part>

Adds to or subtracts from the date/time expr the integral amount units of type part. This mirrors the behavior of the TIMESTAMPADD function, only with a different format and different date/time part constants. The following date/time constants are supported for part:

Constant	Description
`YEAR`	Year is modified by interval amount (not affected by leap year, etc.)
`QUARTER`	Month is modified by three times the interval amount, irrespective of the number of days in the months between; day adjusting performed the same as the `MONTH` description, but only on final month (e.g., Jan 31st + 1 quarter will be Apr 30th, not Apr 28th because of February)
`MONTH`	Month is modified by interval amount and date adjusted if overflow/underflow occurs; day adjusted to last day of calculated month if not a valid day for that month (e.g. Apr 31st -> Apr 30th)
`WEEK`	Day is modified by 7 times the interval amount (time not affected by daylight savings time, etc.); month & year are adjusted, if overflow/underflow occurs
`DAY`	Day is modified by interval amount (time not affected by daylight savings time, etc.); date is adjusted, if overflow/underflow occurs
`HOUR`	Hour is modified by interval amount (time not affected by daylight savings time, etc.); date is adjusted, if overflow/underflow occurs
`MINUTE`	Minute is modified by interval amount; date/time are adjusted, if overflow/underflow occurs
`SECOND`	Second is modified by interval amount; date/time are adjusted, if overflow/underflow occurs

LAST_DAY(date)

Returns the date of the last day of the month in the given date

MINUTE(expr)

Extracts the minute of the day from expr [0 - 59]

MONTH(expr)

Extracts the month of the year from expr [1 - 12]

MONTHNAME(expr)

Extracts the month of the year from expr and converts it to the corresponding month name [January - December]

MSEC(expr)

Extracts the millisecond of the second from expr [0 - 999]

MSECS_SINCE_EPOCH(expr)

Converts expr date/time to the number of milliseconds since the epochExample:

Function Call	`MSECS_SINCE_EPOCH(‘2000-10-10 12:34:56.789’)`
Return	`971181296789`

NEXT_DAY(date, day_of_week)

Returns the date of the next day of the week, provided as a day name in day_of_week, that occurs after the given dateSome examples, given that 2000-10-10 is a Tuesday:

Function Call	Result
`NEXT_DAY(‘2000-10-10’, ‘Wednesday’)`	`2000-10-11`
`NEXT_DAY(‘2000-10-10’, ‘Friday’)`	`2000-10-13`
`NEXT_DAY(‘2000-10-10’, ‘Tuesday’)`	`2000-10-17`

NOW()

Alias for CURRENT_DATETIME()

QUARTER(expr)

Extracts the quarter of the year from expr [1 - 4]

Expression Value	Result
Date in January, February, or March	`1`
Date in April, May, or June	`2`
Date in July, August, or September	`3`
Date in October, November, or December	`4`

SECOND(expr)

Extracts the seconds of the minute from expr [0 - 59]

SEC(expr)

Alias for SECOND(expr)

SECS_SINCE_EPOCH(expr)

Converts expr date/time to the number of seconds since the epochExample:

Function Call	`SECS_SINCE_EPOCH(‘2000-10-10 12:34:56’)`
Return	`971181296`

SLEEP(expr)

Pause execution for at least expr seconds; though system load may delay the return from this call for longer than the specified amount. Use a decimal for expr to pause for less than a second; e.g., SLEEP(0.001) will pause for at least 1 millisecond. SLEEP should be invoked in a Tableless Query to avoid being called for every record in a result set; e.g.:

SELECT SLEEP(0.001)

TIME_BUCKET (width, ts[, offset[, base]])

Calculates the date/time range in which a given timestamp ts falls, based on a set of fixed-width “buckets” with the given width, start-aligned base date/time, and offset from that base date/timeThe width is the number of milliseconds each bucket should span. An INTERVAL can also be used to specify the width.The offset is the number of milliseconds after (positive offset) or number of milliseconds before (negative offset) the base date/time to which the buckets should be aligned. An INTERVAL can also be used to specify the offset. The default is no offset.The default base is 2000-01-03 00:00:00.Typically, TIME_BUCKET is used in the following type of query:

SELECT
    TIME_BUCKET(
        INTERVAL 5 MINUTES,
        ts,
        INTERVAL -2.5 MINUTES,
        '2023-02-28'
    ) + INTERVAL 2.5 MINUTES AS five_minute_midpoint,
    AVG(cpu) AS avg_cpu
FROM example.host_metrics
GROUP BY five_minute_midpoint
ORDER BY five_minute_midpoint

The result will be as follows:

Timestamps in the ts column of the example.host_metrics table will be grouped into buckets
Each bucket will span a 5 minute interval
The baseline bucket will start at 2023-02-27 23:57:30 (2023-02-28 offset by -2.5 minutes) and continue through 2023-02-28 00:02:30 (5 minutes from 2023-02-27 23:57:30)
Buckets will extend before & after the baseline bucket in contiguous, non-overlapping fashion
Each result record will show the timestamp in the middle of the bucket’s range (+ INTERVAL 2.5 MINUTES from the start of each 5 minute span) and the average CPU usage across the records contained within that date/time range
Gaps in the data will not be filled in with empty buckets—only buckets containing the timestamps found in the ts column of example.host_metrics will be returned in the result set

TIMESTAMPADD (unit, amount, expr)

Adds the positive or negative integral amount of unit date/time intervals to the date/time in exprThe following date/time intervals are supported for unit:

Constant	Description
`YEAR`	Year is modified by interval amount (not affected by leap year, etc.)
`QUARTER`	Month is modified by three times the interval amount, irrespective of the number of days in the months between; day adjusting performed the same as the `MONTH` description, but only on final month (e.g., Jan 31st + 1 quarter will be Apr 30th, not Apr 28th because of February)
`MONTH`	Month is modified by interval amount and date adjusted if overflow/underflow occurs; day adjusted to last day of calculated month if not a valid day for that month (e.g. Apr 31st -> Apr 30th)
`WEEK`	Day is modified by 7 times the interval amount (time not affected by daylight savings time, etc.); month & year are adjusted, if overflow/underflow occurs
`DAY`	Day is modified by interval amount (time not affected by daylight savings time, etc.); date is adjusted, if overflow/underflow occurs
`HOUR`	Hour is modified by interval amount (time not affected by daylight savings time, etc.); date is adjusted, if overflow/underflow occurs
`MINUTE`	Minute is modified by interval amount; date/time are adjusted, if overflow/underflow occurs
`SECOND`	Second is modified by interval amount; date/time are adjusted, if overflow/underflow occurs
`MILLISECOND`	Millisecond is modified by interval amount; date/time are adjusted, if overflow/underflow occurs
`FRAC_SECOND`	Nanosecond is modified by interval amount; date/time are adjusted, if overflow/underflow occurs Time is processed to millisecond precision, so any portion of an `amount` with finer granularity than 1,000,000 nanoseconds will be ignored (e.g., requesting the addition of 1,234,567 nanoseconds will result in 1 millisecond actually being added)

Any of these unit types can have a SQL_TSI_ prefix prepended to them; e.g., both DAY and SQL_TSI_DAY are valid unit types for specifying a day interval.

Examples:

Function Call	Result
`TIMESTAMPADD(SQL_TSI_YEAR, 1, ‘2000-10-10’)`	`2001-10-10`
`TIMESTAMPADD(SQL_TSI_QUARTER, 1, ‘2000-11-30’)`	`2001-02-28`
`TIMESTAMPADD(SQL_TSI_MONTH, 1, ‘2000-01-31’)`	`2000-02-29`
`TIMESTAMPADD(SQL_TSI_WEEK, 53, ‘2000-01-01’)`	`2001-01-06`
`TIMESTAMPADD(SQL_TSI_DAY, 1, ‘2000-12-31’)`	`2001-01-01`
`TIMESTAMPADD(SQL_TSI_HOUR, 12, ‘2000-10-10 12:34:56’)`	`2000-10-11 00:34:56.000`
`TIMESTAMPADD(SQL_TSI_MINUTE, 1, ‘2000-10-10 12:34:56’)`	`2000-10-10 12:35:56.000`
`TIMESTAMPADD(SQL_TSI_SECOND, 1, ‘2000-12-31 23:59:59’)`	`2001-01-01 00:00:00.000`
`TIMESTAMPADD(SQL_TSI_MILLISECOND, 1, ‘2000-10-10 12:34:56’)`	`2000-10-10 12:34:56.001`
`TIMESTAMPADD(SQL_TSI_FRAC_SECOND, 1000000, ‘2000-10-10 12:34:56’)`	`2000-10-10 12:34:56.001`
`TIMESTAMPADD(SQL_TSI_SECOND, 1, TIME ‘12:34:56’)`	`12:34:57.000`

TIMESTAMPDIFF (unit, begin, end)

This is not symmetric with TIMESTAMPADD in all cases, as adding 1 MONTH to Mar 31st results in Apr 30th, but the TIMESTAMPDIFF in MONTH units between those two dates is 0.

Examples:

Function Call	Result
`TIMESTAMPDIFF(MONTH, DATETIME(‘2000-10-10 01:23:45.678’), DATETIME(‘2000-12-31 12:34:56.789’))`	`2`
`TIMESTAMPDIFF(MONTH, DATETIME(‘2000-03-31 01:23:45.678’), DATETIME(‘2000-04-30 12:34:56.789’))`	`0`
`TIMESTAMPDIFF(MONTH, DATETIME(‘2000-12-31 01:23:45.678’), DATETIME(‘2000-10-10 12:34:56.789’))`	`-2`
`TIMESTAMPDIFF(HOUR, DATETIME(‘2000-10-10 12:34:56.789’), TIMESTAMP(978222896678))`	`1955`

TIMESTAMP_TRUNC(part, expr)

Alias for DATE_TRUNC(part, expr)

UNIX_TIMESTAMP(expr)

Alias for SECS_SINCE_EPOCH(expr)

WEEK(expr)

Extracts the week of the year from expr [1 - 54]; each full week starts on Sunday (a 1 is returned for the week containing Jan 1st)

YEAR(expr)

Extracts the year from expr; 4-digit year, A.D.

Function Call	`DATE_TO_EPOCH_MSECS(2017, 06, 15, 09, 22, 15, 42)`
Return	`1497518535042`
Resolves To	Thursday, June 15, 2017 9:22:15.042 AM

Function Call	`DATE_TO_EPOCH_SECS(2017, 06, 15, 09, 22, 15)`
Return	`1494926535`
Resolves To	Thursday, June 15, 2017 9:22:15.042 AM

Function Call	`WEEK_TO_EPOCH_MSECS(2017,-32)`
Return	`1463270400000`
Resolves To	Sunday, May 15, 2016 12:00:00 AM

Function Call	`WEEK_TO_EPOCH_SECS(2017,-32)`
Return	`1463270400`
Resolves To	Sunday, May 15, 2016 12:00:00 AM

Documentation

Date/Time Base Functions

Date/Time Complex Conversion Functions

Function Call	`TIMESTAMP_FROM_DATE_TIME(‘2017-06-15’, ‘10:37:30’)`
Return	`2017-06-15 10:37:30.000`

​Date/Time Base Functions

​Date/Time Complex Conversion Functions

Date/Time Base Functions

Date/Time Complex Conversion Functions