Date and time
Date and time functions perform operations on date and time values to calculate, extract, or format temporal information and return a result.
The following functions are available in Qlik Open Lakehouse:
| Function | Description |
|---|---|
| ADD_TIME_ZONE_OFFSET | Add a timezone offset to a date. The timezone offset can be in any of the standard formats accepted by Java's ZoneId.of method (America/New_York, +0200, etc.) |
| DATE | Casts string to date |
| DATE_ADD | Adds an interval value of type unit to timestamp. Subtraction can be performed by using a negative value |
| DATE_DIFF | Returns timestamp2 - timestamp1 expressed in terms of unit |
| DATE_TRUNC | Truncate a date to a given unit |
| DAY | Returns the day of the month from the timestamp |
| DAY_OF_WEEK | Returns the ISO day of the week from the timestamp. values range from 1 (Monday) to 7 (Sunday) |
| DAY_OF_YEAR | Returns the day of the year from timestamp. values range from 1 to 366 |
| EXTRACT_TIMESTAMP | Convert a date or timestamp string to a date or timestamp, autodetecting the date format |
| FORMAT_DATETIME | Convert a datetime into a string |
| FROM_ISO8601_DATE | Parses the ISO 8601 formatted string into a date |
| FROM_UNIXTIME | Convert epoch seconds to a timestamp |
| HOUR | Returns the hour of the day from a timestamp. Values range from 0 to 23 |
| MILLISECOND | Returns the millisecond of the second from the timestamp |
| MINUTE | Returns the minute of the hour from the timestamp |
| MONTH | Returns the month of the year from the timestamp |
| QUARTER | Returns the quarter of the year from the timestamp. Values range from 1 to 4 |
| RUN_END_TIME | Returns the job execution end time |
| RUN_START_TIME | Returns the job execution start time |
| SECOND | Returns the second of the minute from the timestamp |
| SUBTRACT_TIME_ZONE_OFFSET | Subtract a timezone offset from a date. The timezone offset can be in any of the standard formats accepted by Java's `ZoneId.of` method (America/New_York, +0200, etc) |
| TO_UNIXTIME | Convert a date to its Epoch (Unix) seconds representation, with fractional milliseconds |
| TO_UNIX_EPOCH_MILLIS | Convert a date to its Epoch (Unix) seconds representation |
| TO_UNIX_EPOCH_SECONDS | Convert a date to its Epoch (Unix) seconds representation |
| WEEK | Returns the ISO week of the year from the timestamp. Values range from 1 to 53 |
| YEAR | Returns the year from the timestamp |
| YEAR_OF_WEEK | Returns the year of ISO week from the timestamp |