# Datetime Functions in Confluent Cloud for Apache Flink Confluent Cloud for Apache Flink® provides these built-in functions for handling date and time logic in SQL queries: | Date | Time | Timestamp | Utility | |--------------------------------------------------|--------------------------------------------------|--------------------------------------------------------------------|------------------------------------------------------------| | [CURRENT_DATE](#flink-sql-current-date-function) | [CONVERT_TZ](#flink-sql-convert-tz-function) | [CURRENT_TIMESTAMP](#flink-sql-current-timestamp-function) | [CEIL](#flink-sql-ceil-timepoint-function) | | [DATE_FORMAT](#flink-sql-date-format-function) | [CURRENT_TIME](#flink-sql-current-time-function) | [CURRENT_ROW_TIMESTAMP](#flink-sql-current-row-timestamp-function) | [CURRENT_WATERMARK](#flink-sql-current-watermark-function) | | [DATE](#flink-sql-date-function) | [HOUR](#flink-sql-hour-function) | [LOCALTIMESTAMP](#flink-sql-localtimestamp-function) | [EXTRACT](#flink-sql-extract-function) | | [DAYOFMONTH](#flink-sql-day-of-month-function) | [LOCALTIME](#flink-sql-localtime-function) | [TIMESTAMP](#flink-sql-timestamp-function) | [FLOOR](#flink-sql-floor-timepoint-function) | | [DAYOFWEEK](#flink-sql-day-of-week-function) | [MINUTE](#flink-sql-minute-function) | [TO_TIMESTAMP](#flink-sql-to-timestamp-function) | [FROM_UNIXTIME](#flink-sql-from-unixtime-function) | | [DAYOFYEAR](#flink-sql-day-of-year-function) | [NOW](#flink-sql-now-function) | [TO_TIMESTAMP_LTZ](#flink-sql-to-timestamp-ltz-function) | [INTERVAL](#flink-sql-interval-function) | | [MONTH](#flink-sql-month-function) | [SECOND](#flink-sql-second-function) | [TIMESTAMPADD](#flink-sql-timestampadd-function) | [SOURCE_WATERMARK](#flink-sql-source-watermark-function) | | [QUARTER](#flink-sql-quarter-function) | [TIME](#flink-sql-time-function) | [TIMESTAMPDIFF](#flink-sql-timestampdiff-function) | [OVERLAPS](#flink-sql-overlaps-function) | | [TO_DATE](#flink-sql-to-date-function) | | [UNIX_TIMESTAMP](#flink-sql-unix-timestamp-function) | | | [WEEK](#flink-sql-week-function) | | [UNIX_TIMESTAMP](#flink-sql-unix-timestamp-2-function) | | | [YEAR](#flink-sql-year-function) | | | | ## Time interval and point unit specifiers The following table lists specifiers for time interval and time point units. Plural works for SQL only. | Time interval unit | Time point unit | |--------------------------|---------------------------------| | `MILLENNIUM` | – | | `CENTURY` | – | | `DECADE` | – | | `YEAR(S)` | `YEAR` | | `YEAR(S) TO MONTH(S)` | – | | `QUARTER(S)` | `QUARTER` | | `MONTH(S)` | `MONTH` | | `WEEK(S)` | `WEEK` | | `DAY(S)` | `DAY` | | `DAY(S) TO HOUR(S)` | – | | `DAY(S) TO MINUTE(S)` | – | | `DAY(S) TO SECOND(S)` | – | | `HOUR(S)` | `HOUR` | | `HOUR(S) TO MINUTE(S)` | – | | `HOUR(S) TO SECOND(S)` | – | | `MINUTE(S)` | `MINUTE` | | `MINUTE(S) TO SECOND(S)` | – | | `SECOND(S)` | `SECOND` | | `MILLISECOND` | `MILLISECOND` | | `MICROSECOND` | `MICROSECOND` | | `NANOSECOND` | – | | `EPOCH` | – | | `DOY` *(SQL only)* | – | | `DOW` *(SQL only)* | – | | `ISODOW` *(SQL only)* | – | | `ISOYEAR` *(SQL only)* | – | | – | `SQL_TSI_YEAR` *(SQL only)* | | – | `SQL_TSI_QUARTER` *(SQL only)* | | – | `SQL_TSI_MONTH` *(SQL only)* | | – | `SQL_TSI_WEEK` *(SQL only)* | | – | `SQL_TSI_DAY` *(SQL only)* | | – | `SQL_TSI_HOUR` *(SQL only)* | | – | `SQL_TSI_MINUTE` *(SQL only)* | | – | `SQL_TSI_SECOND` *(SQL only)* | ### CEIL Rounds a time point up. Syntax : ```sql CEIL(timepoint TO timeintervalunit) ``` Description : The `CEIL` function returns a value that rounds `timepoint` up to the time unit specified by `timeintervalunit`. Example : ```sql -- returns "12:45:00" SELECT CEIL(TIME '12:44:31' TO MINUTE); ``` Related function : - [FLOOR](#flink-sql-floor-timepoint-function) ### CONVERT_TZ Converts a datetime from one time zone to another. Syntax : ```sql CONVERT_TZ(string1, string2, string3) ``` Description : The `CONVERT_TZ` function converts a datetime `string1` that has the default ISO timestamp format, “yyyy-MM-dd hh:mm:ss”, from the time zone specified by `string2` to the time zone specified by `string3`.
The format of the time zone arguments is either an abbreviation, like “PST”, a full name, like “America/Los_Angeles”, or a custom ID, like “GMT-08:00”. Example : ```sql -- returns "1969-12-31 16:00:00" SELECT CONVERT_TZ('1970-01-01 00:00:00', 'UTC', 'America/Los_Angeles'); ``` ### CURRENT_DATE Returns the current date. Syntax : ```sql CURRENT_DATE ``` Description : The `CURRENT_DATE` function returns the current SQL date in the local time zone.
- In streaming mode, the current date is evaluated for each record. - In batch mode, the current date is evaluated once when the query starts, and `CURRENT_DATE` returns the same result for every row. Example : ```sql -- returns the current date SELECT CURRENT_DATE; ``` ### CURRENT_ROW_TIMESTAMP Returns the current timestamp for each row. Syntax : ```sql CURRENT_ROW_TIMESTAMP() ``` Description : The `CURRENT_ROW_TIMESTAMP` function returns the current SQL timestamp in the local time zone. The return type is `TIMESTAMP_LTZ(3)`.
The timestamp is evaluated for each row, in both batch and streaming mode. Example : ```sql -- returns the timestamp of the current datetime SELECT CURRENT_ROW_TIMESTAMP(); ``` ### CURRENT_TIME Syntax : ```sql CURRENT_TIME ``` Description : The `CURRENT_TIME` function returns the current SQL time in the local time zone.
The `CURRENT_TIME` function is equivalent to [LOCALTIME](#flink-sql-localtime-function). Example : ```sql -- returns the current time, for example: -- 13:03:56 SELECT CURRENT_TIME; ``` ### CURRENT_TIMESTAMP Syntax : ```sql CURRENT_TIMESTAMP ``` Description : The `CURRENT_TIMESTAMP` function returns the current SQL timestamp in the local time zone. The return type is `TIMESTAMP_LTZ(3)`.
- In streaming mode, the current timestamp is evaluated for each record. - In batch mode, the current timestamp is evaluated once when the query starts, and `CURRENT_TIMESTAMP` returns the same result for every row.
The `CURRENT_TIMESTAMP` function is equivalent to [NOW](#flink-sql-now-function). Example : ```sql -- returns the current timestamp, for example: -- 2023-10-16 13:04:58.081 SELECT CURRENT_TIMESTAMP; ``` ### CURRENT_WATERMARK Gets the current [watermark](../../../_glossary.md#term-watermark) for a `rowtime` column. Syntax : ```sql CURRENT_WATERMARK(rowtime) ``` Description : The `CURRENT_WATERMARK` function returns the current watermark for the given `rowtime` attribute, or NULL if no common watermark of all upstream operations is available at the current operation in the pipeline.
The return type of the function is inferred to match that of the provided `rowtime` attribute, but with an adjusted precision of *3*.
For example, if the rowtime attribute is `TIMESTAMP_LTZ(9)`, the function returns `TIMESTAMP_LTZ(3)`.
This function can return NULL; account for this case in your query.
For more information, see [watermarks](../../concepts/timely-stream-processing.md#flink-sql-event-time-and-watermarks). Example : The following example shows how to filter out late data by using the `CURRENT_WATERMARK` function with a `rowtime` column named `ts`.
```sql WHERE CURRENT_WATERMARK(ts) IS NULL OR ts > CURRENT_WATERMARK(ts) ``` Related function : - [SOURCE_WATERMARK](#flink-sql-source-watermark-function) ### DATE_FORMAT Converts a timestamp to a formatted string. Syntax : ```sql DATE_FORMAT(timestamp, date_format) ``` Description : The `DATE_FORMAT` function converts the specified timestamp to a string value in the format specified by the `date_format` string.
The format string is compatible with the Java [SimpleDateFormat](https://docs.oracle.com/en/java/javase/19/docs/api/java.base/java/text/SimpleDateFormat.html). class. Example : ```sql -- returns "5:32 PM, UTC" SELECT DATE_FORMAT('2023-03-15 17:32:01.009', 'K:mm a, z'); ``` ### DATE Parses a DATE from a string. Syntax : ```sql DATE string ``` Description : The `DATE` function returns a SQL date parsed from the specified string.
The date format of the input string must be “yyyy-MM-dd”. Example : ```sql -- returns "2023-05-23" SELECT DATE '2023-05-23'; ``` ### DAYOFMONTH Gets the day of month from a DATE. Syntax : ```sql DAYOFMONTH(date) ``` Description : The `DAYOFMONTH` function returns the day of a month from the specified SQL DATE as an integer between *1* and *31*.
The `DAYOFMONTH` function is equivalent to `EXTRACT(DAY FROM date)`. Example : ```sql -- returns 27 SELECT DAYOFMONTH(DATE '1994-09-27'); ``` ### DAYOFWEEK Gets the day of week from a DATE. Syntax : ```sql DAYOFWEEK(date) ``` Description : The `DAYOFWEEK` function returns the day of a week from the specified SQL DATE as an integer between *1* and *7*.
The `DAYOFWEEK` function is equivalent to `EXTRACT(DOW FROM date)`. Example : ```sql -- returns 3 SELECT DAYOFWEEK(DATE '1994-09-27'); ``` ### DAYOFYEAR Gets the day of year from a DATE. Syntax : ```sql DAYOFYEAR(date) ``` Description : The `DAYOFYEAR` function returns the day of a year from the specified SQL DATE as an integer between *1* and *366*.
The `DAYOFYEAR` function is equivalent to `EXTRACT(DOY FROM date)`. Example : ```sql -- returns 270 SELECT DAYOFYEAR(DATE '1994-09-27'); ``` ### EXTRACT Gets a time interval unit from a datetime. Syntax : ```sql EXTRACT(timeintervalunit FROM temporal) ``` Description : The `EXTRACT` function returns a LONG value extracted from the specified `timeintervalunit` part of `temporal`. Example : ```sql -- returns 5 SELECT EXTRACT(DAY FROM DATE '2006-06-05'); ``` Related functions : - [DAYOFMONTH](#flink-sql-day-of-month-function) - [DAYOFWEEK](#flink-sql-day-of-week-function) - [DAYOFYEAR](#flink-sql-day-of-year-function) ### FLOOR Rounds a time point down. Syntax : ```sql FLOOR(timepoint TO timeintervalunit) ``` Description : The `FLOOR` function returns a value that rounds `timepoint` down to the time unit specified by `timeintervalunit`. Example : ```sql -- returns 12:44:00 SELECT FLOOR(TIME '12:44:31' TO MINUTE); ``` Related function : - [CEIL](#flink-sql-ceil-timepoint-function) ### FROM_UNIXTIME Gets a Unix time as a formatted string. Syntax : ```sql FROM_UNIXTIME(numeric[, string]) ``` Description : The `FROM_UNIXTIME` function returns a representation of the NUMERIC argument as a value in string format. The default format is “yyyy-MM-dd hh:mm:ss”.
The specified NUMERIC is an internal timestamp value representing seconds since “1970-01-01 00:00:00” UTC, such as produced by the [UNIX_TIMESTAMP](#flink-sql-unix-timestamp-function) function.
The return value is expressed in the session time zone (specified in the table configuration). Example : ```sql -- Returns "1970-01-01 00:00:44" if in the UTC time zone, -- but returns "1970-01-01 09:00:44" if in the 'Asia/Tokyo' time zone. SELECT FROM_UNIXTIME(44); ``` ### HOUR Gets the hour of day from a timestamp. Syntax : ```sql HOUR(timestamp) ``` Description : The `HOUR` function returns the hour of a day from the specified SQL timestamp as an integer between *0* and *23*.
The `HOUR` function is equivalent to `EXTRACT(HOUR FROM timestamp)`. Example : ```sql -- returns 13 SELECT HOUR(TIMESTAMP '1994-09-27 13:14:15'); ``` Related functions : - [MINUTE](#flink-sql-minute-function) - [SECOND](#flink-sql-second-function) ### INTERVAL Parses an interval string. Syntax : ```sql INTERVAL string range ``` Description : The `INTERVAL` function parses an interval string in the form “dd hh:mm:ss.fff” for SQL intervals of milliseconds, or “yyyy-mm” for SQL intervals of months.
For intervals of milliseconds, these interval ranges apply:
- DAY - MINUTE - DAY TO HOUR - DAY TO SECOND
For intervals of months, these interval ranges apply:
- YEAR - YEAR TO MONTH Examples : The following SELECT statements return the values indicated in the comment lines.
```sql -- returns +10 00:00:00.004 SELECT INTERVAL '10 00:00:00.004' DAY TO SECOND;
-- returns +10 00:00:00.000 SELECT INTERVAL '10' DAY;
-- returns +2-10 SELECT INTERVAL '2-10' YEAR TO MONTH; ``` ### LOCALTIME Gets the current local time. Syntax : ```sql LOCALTIME ``` Description : The `LOCALTIME` function returns the current SQL time in the local time zone. The return type is `TIME(0)`.
- In streaming mode, the current local time is evaluated for each record. - In batch mode, the current local time is evaluated once when the query starts, and `LOCALTIME` returns the same result for every row. Example : ```sql -- returns the local machine time as "hh:mm:ss", for example: -- 13:16:03 SELECT LOCALTIME; ``` ### LOCALTIMESTAMP Gets the current timestamp. Syntax : ```sql LOCALTIMESTAMP ``` Description : The `LOCALTIMESTAMP` function returns the current SQL timestamp in local time zone. The return type is `TIMESTAMP(3)`.
- In streaming mode, the current timestamp is evaluated for each record. - In batch mode, the current timestamp is evaluated once when the query starts, and `LOCALTIMESTAMP` returns the same result for every row. Example : ```sql -- returns the local machine datetime as "yyyy-mm-dd hh:mm:ss.sss", for example: -- 2023-10-16 13:15:32.390 SELECT LOCALTIMESTAMP; ``` ### MINUTE Gets the minute of hour from a timestamp. Syntax : ```sql MINUTE(timestamp) ``` Description : The `MINUTE` function returns the minute of an hour from the specified SQL timestamp as an integer between *0* and *59*.
The `MINUTE` function is equivalent to `EXTRACT(MINUTE FROM timestamp)`. Example : ```sql - returns 14 SELECT MINUTE(TIMESTAMP '1994-09-27 13:14:15'); ``` Related functions : - [HOUR](#flink-sql-hour-function) - [SECOND](#flink-sql-second-function) ### MONTH Gets the month of year from a DATE. Syntax : ```sql MONTH(date) ``` Description : The `MONTH` function returns the month of a year from the specified SQL date as an integer between *1* and *12*.
The `MONTH` function is equivalent to `EXTRACT(MONTH FROM date)`. Example : ```sql -- returns 9 SELECT MONTH(DATE '1994-09-27'); ``` Related functions : - [DAYOFMONTH](#flink-sql-day-of-month-function) - [DAYOFYEAR](#flink-sql-day-of-year-function) - [WEEK](#flink-sql-week-function) - [YEAR](#flink-sql-year-function) ### NOW Gets the current timestamp. Syntax : ```sql NOW() ``` Description : The `NOW` function returns the current SQL timestamp in the local time zone.
The `NOW` function is equivalent to [CURRENT_TIMESTAMP](#flink-sql-current-timestamp-function). Example : ```sql -- returns the local machine datetime as "yyyy-mm-dd hh:mm:ss.sss", for example: -- 2023-10-16 13:17:54.382 SELECT NOW(); ``` ### OVERLAPS Checks whether two time intervals overlap. Syntax : ```sql (timepoint1, temporal1) OVERLAPS (timepoint2, temporal2) ``` Description : The `OVERLAPS` function returns TRUE if two time intervals defined by `(timepoint1, temporal1)` and `(timepoint2, temporal2)` overlap.
The temporal values can be either a time point or a time interval. Example : ```sql -- returns TRUE SELECT (TIME '2:55:00', INTERVAL '1' HOUR) OVERLAPS (TIME '3:30:00', INTERVAL '2' HOUR);
-- returns FALSE SELECT (TIME '9:00:00', TIME '10:00:00') OVERLAPS (TIME '10:15:00', INTERVAL '3' HOUR); ``` ### QUARTER Gets the quarter of year from a DATE. Syntax : ```sql QUARTER(date) ``` Description : The `QUARTER` function returns the quarter of a year from the specified SQL DATE as an integer between *1* and *4*.
The `QUARTER` function is equivalent to `EXTRACT(QUARTER FROM date)`. Example : ```sql -- returns 3 SELECT QUARTER(DATE '1994-09-27'); ``` Related functions : - [DAYOFMONTH](#flink-sql-day-of-month-function) - [DAYOFYEAR](#flink-sql-day-of-year-function) - [WEEK](#flink-sql-week-function) - [YEAR](#flink-sql-year-function) ### SECOND Gets the second of minute from a TIMESTAMP. Syntax : ```sql SECOND(timestamp) ``` Description : The `SECOND` function returns the second of a minute from the specified SQL TIMESTAMP as an integer between *0* and *59*.
The `SECOND` function is equivalent to `EXTRACT(SECOND FROM timestamp)`. Example : ```sql -- returns 15 SELECT SECOND(TIMESTAMP '1994-09-27 13:14:15'); ``` Related functions : - [HOUR](#flink-sql-hour-function) - [MINUTE](#flink-sql-minute-function) ### SOURCE_WATERMARK Provides a default [watermark](../../../_glossary.md#term-watermark) strategy. Syntax : ```sql WATERMARK FOR column AS SOURCE_WATERMARK() ``` Description : The `SOURCE_WATERMARK` function provides a default watermark strategy.
Watermarks are assigned per Kafka partition in the source operator. The watermark for each partition is the maximum event time seen so far, minus a fixed out-of-orderness tolerance of 180 milliseconds.
The 180ms tolerance is based on the approximate P99 cross-region network latency. This means the strategy tolerates minor delays in event delivery without dropping events as late.
Unlike previous versions, no warmup period or minimum record count is required. Watermarks begin advancing immediately as data arrives.
Additionally, when a partition stops receiving data and becomes idle, it forwards its latest event time before it is excluded from watermark calculations. This means that a single idle partition no longer blocks your query from producing results.
If your data has out-of-orderness that exceeds 180ms, define a [custom watermark strategy](../statements/create-table.md#flink-sql-watermark-clause).
For handling idle partitions, see [progressive idleness detection](../statements/create-table.md#flink-sql-watermark-clause-progressive-idleness). Example : ```mysql -- Create a table that has the default watermark strategy -- on the ts column. CREATE TABLE t2 ( i INT, ts TIMESTAMP_LTZ(3), WATERMARK FOR ts AS SOURCE_WATERMARK());
-- The queryable schema for the table has the default watermark -- strategy on the ts column. ( i INT, ts TIMESTAMP_LTZ(3), `$rowtime` TIMESTAMP_LTZ(3) NOT NULL METADATA VIRTUAL COMMENT 'SYSTEM', WATERMARK FOR ts AS SOURCE_WATERMARK() ); ``` Related functions : - [CURRENT_WATERMARK](#flink-sql-current-watermark-function) - [Watermark clause](../statements/create-table.md#flink-sql-watermark-clause) ### TIME Parses a string to a TIME. Syntax : ```sql TIME string ``` Description : The `TIME` function returns a SQL TIME parsed from the specified string.
The time format of the input string must be “hh:mm:ss”. Example : ```sql -- returns 23:42:55 as a TIME SELECT TIME '23:42:55'; ``` ### TIMESTAMP Syntax : ```sql TIMESTAMP string ``` Description : The `TIMESTAMP` function returns a SQL TIMESTAMP parsed from the specified string.
The timestamp format of the input string must be “yyyy-MM-dd hh:mm:ss[.SSS]”. Example : ```sql -- returns 2023-05-04 23:42:55 as a TIMESTAMP SELECT TIMESTAMP '2023-05-04 23:42:55'; ``` ### TO_DATE Converts a date string to a DATE. Syntax : ```sql TO_DATE(string1[, string2]) ``` Description : The `TO_DATE` function converts the date string `string1` with format `string2` to a DATE.
The default format is ‘yyyy-mm-dd’. Example : ```sql -- returns 2023-05-04 as a DATE SELECT TO_DATE('2023-05-04'); ``` ### TO_TIMESTAMP Converts a date string to a TIMESTAMP. Syntax : ```sql TO_TIMESTAMP(string1[, string2]) ``` Description : The `TO_TIMESTAMP` function converts datetime string `string1` with format `string2` under the ‘UTC+0’ time zone to a TIMESTAMP.
The default format is ‘yyyy-mm-dd hh:mm:ss’. Example : ```sql -- returns 2023-05-04 23:42:55.000 as a TIMESTAMP SELECT TO_TIMESTAMP('2023-05-04 23:42:55', 'yyyy-mm-dd hh:mm:ss'); ``` ### TO_TIMESTAMP_LTZ Converts a Unix time to a `TIMESTAMP_LTZ`. Syntax : ```sql TO_TIMESTAMP_LTZ(numeric, precision) TO_TIMESTAMP_LTZ(string1[, string2[, string3]]) ``` Description : The first version of the `TO_TIMESTAMP_LTZ` function converts Unix epoch seconds or epoch milliseconds to a `TIMESTAMP_LTZ`.
These are the valid precision values:
- **0**, which represents `TO_TIMESTAMP_LTZ(epoch_seconds, 0)` - **3**, which represents `TO_TIMESTAMP_LTZ(epoch_milliseconds, 3)`
If no precision is provided, the default precision is 3.
The second version converts a timestamp string `string1` with format `string2` (by default ‘yyyy-MM-dd HH:mm:ss.SSS’) in time zone `string3` (by default ‘UTC’) to a TIMESTAMP_LTZ.
If any input is NULL, the function returns NULL. Examples : ```sql -- convert 1000 epoch seconds -- returns 1970-01-01 00:16:40.000 as a TIMESTAMP_LTZ SELECT TO_TIMESTAMP_LTZ(1000, 0);
-- convert 1000 epoch milliseconds -- returns 1970-01-01 00:00:01.000 as a TIMESTAMP_LTZ SELECT TO_TIMESTAMP_LTZ(1000, 3);
-- convert timestamp string with custom format and timezone -- returns appropriate TIMESTAMP_LTZ based on the timezone SELECT TO_TIMESTAMP_LTZ('2023-05-04 12:00:00', 'yyyy-MM-dd HH:mm:ss', 'America/Los_Angeles'); ``` ### TIMESTAMPADD Adds a time interval to a datetime. Syntax : ```sql TIMESTAMPADD(timeintervalunit, interval, timepoint) ``` Description : Returns the sum of `timepoint` and the `interval` number of time units specified by `timeintervalunit`.
The unit for the interval is given by the first argument, which must be one of the following values:
- DAY - HOUR - MINUTE - MONTH - SECOND - YEAR Example : ```sql -- returns 2000-01-01 SELECT TIMESTAMPADD(DAY, 1, DATE '1999-12-31');
-- returns 2000-01-01 01:00:00 SELECT TIMESTAMPADD(HOUR, 2, TIMESTAMP '1999-12-31 23:00:00'); ``` ### TIMESTAMPDIFF Computes the interval between two datetimes. Syntax : ```sql TIMESTAMPDIFF(timepointunit, timepoint1, timepoint2) ``` Description : The `TIMESTAMPDIFF` function returns the (signed) number of `timepointunit` between `timepoint1` and `timepoint2`.
The unit for the interval is given by the first argument, which must be one of the following values:
- DAY - HOUR - MINUTE - MONTH - SECOND - YEAR Example : ```sql -- returns -1 SELECT TIMESTAMPDIFF(DAY, DATE '2000-01-01', DATE '1999-12-31');
-- returns -2 SELECT TIMESTAMPDIFF(HOUR, TIMESTAMP '2000-01-01 01:00:00', TIMESTAMP '1999-12-31 23:00:00'); ``` ### UNIX_TIMESTAMP Gets the current Unix timestamp in seconds. Syntax : ```sql UNIX_TIMESTAMP() ``` Description : The `UNIX_TIMESTAMP` function is not deterministic, which means the value is recalculated for each row. Example : ```sql -- returns Epoch seconds, for example: -- 1697487923 SELECT UNIX_TIMESTAMP(); ``` ### UNIX_TIMESTAMP Converts a datetime string to a Unix timestamp. Syntax : ```sql UNIX_TIMESTAMP(string1[, string2]) ``` Description : The `UNIX_TIMESTAMP(string)` function converts the specified datetime string `string1` in format `string2` to a Unix timestamp (in seconds), using the time zone specified in the table configuration.
The default format is “yyyy-MM-dd HH:mm:ss”.
If a time zone is specified in the datetime string and parsed by the UTC+X format, like `yyyy-MM-dd HH:mm:ss.SSS X`, this function uses the specified timezone in the datetime string instead of the timezone in the table configuration. If the datetime string can’t be parsed, the default value of `Long.MIN_VALUE(-9223372036854775808)` is returned. Examples : ```sql -- returns 1683201600 SELECT UNIX_TIMESTAMP('2023-05-04 12:00:00');
-- Returns 25201 SELECT UNIX_TIMESTAMP('1970-01-01 08:00:01.001', 'yyyy-MM-dd HH:mm:ss.SSS');
-- Returns 1 SELECT UNIX_TIMESTAMP('1970-01-01 08:00:01.001 +0800', 'yyyy-MM-dd HH:mm:ss.SSS X');
-- Returns 25201 SELECT UNIX_TIMESTAMP('1970-01-01 08:00:01.001 +0800', 'yyyy-MM-dd HH:mm:ss.SSS');
-- Returns -9223372036854775808 SELECT UNIX_TIMESTAMP('1970-01-01 08:00:01.001', 'yyyy-MM-dd HH:mm:ss.SSS X'); ``` ### WEEK Gets the week of year from a DATE. Syntax : ```sql WEEK(date) ``` Description : The `WEEK` function returns the week of a year from the specified SQL DATE as an integer between *1* and *53*.
The `WEEK` function is equivalent to `EXTRACT(WEEK FROM date)`. Example : ```sql -- returns 39 SELECT WEEK(DATE '1994-09-27'); ``` Related functions : - [DAYOFMONTH](#flink-sql-day-of-month-function) - [DAYOFYEAR](#flink-sql-day-of-year-function) - [QUARTER](#flink-sql-quarter-function) - [YEAR](#flink-sql-year-function) ### YEAR Gets the year from a DATE. Syntax : ```sql YEAR(date) ```
The `YEAR` function returns the year from the specified SQL DATE.
The `YEAR` function is equivalent to `EXTRACT(YEAR FROM date)`. Example : ```sql -- returns 1994 SELECT YEAR(DATE '1994-09-27'); ``` Related functions : - [DAYOFMONTH](#flink-sql-day-of-month-function) - [DAYOFYEAR](#flink-sql-day-of-year-function) - [QUARTER](#flink-sql-quarter-function) - [MONTH](#flink-sql-month-function) ### Other built-in functions - [Aggregate Functions](aggregate-functions.md#flink-sql-aggregate-functions) - [Changelog Conversion Functions](changelog-conversion.md#flink-sql-changelog-conversion-functions) - [Collection Functions](collection-functions.md#flink-sql-collection-functions) - [Comparison Functions](comparison-functions.md#flink-sql-comparison-functions) - [Conditional Functions](conditional-functions.md#flink-sql-conditional-functions) - [Datetime Functions](#flink-sql-datetime-functions) - [Hash Functions](hash-functions.md#flink-sql-hash-functions) - [JSON Functions](json-functions.md#flink-sql-json-functions) - [ML Preprocessing Functions](ml-preprocessing-functions.md#flink-sql-ml-preprocessing-functions) - [Model Inference Functions](model-inference-functions.md#flink-sql-model-inference-functions) - [Numeric Functions](numeric-functions.md#flink-sql-numeric-functions) - [Search Functions](search-functions.md#flink-sql-search-functions) - [String Functions](string-functions.md#flink-sql-string-functions) - [Table API Functions](table-api-functions.md#flink-table-api-functions) ### Related content - [User-defined Functions](../../concepts/user-defined-functions.md#flink-sql-udfs) - [Create a User Defined Function](../../how-to-guides/create-udf.md#flink-sql-create-udf) #### NOTE This website includes content developed at the [Apache Software Foundation](https://www.apache.org/) under the terms of the [Apache License v2](https://www.apache.org/licenses/LICENSE-2.0.html).