# Timestamp Functions ## CURRENT\_TIMESTAMP `CURRENT_TIMESTAMP()` **Description** Return the current timestamp. **Result Data Type** `TIMESTAMP` **Example** ``` CURRENT_TIMESTAMP() ``` Result: 2021-04-26 18:23:58.422989 UTC ## TIMESTAMP `TIMESTAMP(timestamp_expression, [timezone])` **Description** Convert a string, date, or datetime expression to a timestamp object. **Input** * **`timestamp_expression`**: a `STRING`, `DATE`, `DATETIME, TIMESTAMP` object or column * **`timezone`**: (optional, `STRING`) The default timezone is UTC. You can optionally specify a [time zone.](https://en.wikipedia.org/wiki/List_of_tz_database_time_zones) **Result Data Type** `TIMESTAMP` **Example** ``` TIMESTAMP("2020-12-25 15:30:00 UTC") ``` Result: 2020-12-25 15:30:00 UTC ## EXTRACT `EXTRACT(date_part FROM timestamp [AT TIME ZONE timezone])` **Description** Return a part of the given timestamp. **Input** * **`date_part`**: can be one of the following: MICROSECOND, MILLISECOND, SECOND, MINUTE, HOUR, DAYOFWEEK, DAY, DAYOFYEAR, WEEK(begins on Sunday), ISOWEEK(begin on Monday), MONTH, QUARTER, YEAR, ISOYEAR, DATE, DATETIME, or TIME (See more [details](/acho-studio/data-prep-projects/applying-actions/tools/sql-editor/supported-math-functions-in-formula/timestamp-time-functions.md#details-about-date_part)) * **`timestamp`**: a `TIMESTAMP` object or column * **`AT TIME ZONE timezone`:** You can optionally specify a [time zone](https://en.wikipedia.org/wiki/List_of_tz_database_time_zones) to convert the timestamp into different timezones before extracting the part of the timestamp. **Return Data Type** `INTEGER`, except when: * `part` is `DATE`, returns a `DATE` object. * `part` is `DATETIME`, returns a `DATETIME` object. * `part` is `TIME`, returns a `TIME` object. **Example** ``` EXTRACT(DAY FROM TIMESTAMP(""2008-12-25 05:30:00+00"")) ``` Result: 25 ``` EXTRACT(DATE FROM TIMESTAMP("2008-12-25 05:30:00+00")) ``` Result: 2008-12-25 ``` EXTRACT(DAY FROM TIMESTAMP("2008-12-25 05:30:00+00") AT TIME ZONE "America/Los_Angeles") ``` Result: 24 ## TIMESTAMP\_ADD `TIMESTAMP_ADD(timestamp, INTERVAL integer date_part)` **Description** Add a specified time interval to the given timestamp. **Input** * **`timestamp`**: a `TIMESTAMP` object or column * **`integer`**: the number of `date_part` * **`date_part`**: The unit of the time interval. It can be one of the following: MICROSECOND, MILLISECOND, SECOND, MINUTE, HOUR, or DAY (See more [details](/acho-studio/data-prep-projects/applying-actions/tools/sql-editor/supported-math-functions-in-formula/timestamp-time-functions.md#details-about-date_part)) **Result Data Type** `TIMESTAMP` **Example** ``` TIMESTAMP_ADD(TIMESTAMP("2020-12-25 15:30:00+00"), INTERVAL 10 MINUTE) ``` Result: 2020-12-25 15:40:00 UTC ## TIMESTAMP\_SUB `TIMESTAMP_SUB(timestamp, INTERVAL integer date_part)` **Description** Subtract a specified time interval to the given timestamp. **Input** * **`timestamp`**: a `TIMESTAMP` object or column * **`integer`**: the number of `date_part` * **`date_part`**: The unit of the time interval. It can be one of the following: MICROSECOND, MILLISECOND, SECOND, MINUTE, HOUR, or DAY (See more [details](/acho-studio/data-prep-projects/applying-actions/tools/sql-editor/supported-math-functions-in-formula/timestamp-time-functions.md#details-about-date_part)) **Result Data Type** `TIMESTAMP` **Example** ``` TIMESTAMP_SUB(TIMESTAMP("2020-12-25 15:30:00+00"), INTERVAL 10 MINUTE) ``` Result: 2020-12-25 15:20:00 UTC ## TIMESTAMP\_DIFF `TIMESTAMP_DIFF(timestamp1, timestamp2, date_part)` **Description** Return the difference between two timestamps (timestamp1-timestamp2). The difference is expressed in units of date\_part. **Input** * **`timestamp1`**: a `TIMESTAMP` object or column * **`timestamp2`**: a `TIMESTAMP` object or column * **`date_part`**: The unit of the difference between two timestamps. It can be one of the following: MICROSECOND, MILLISECOND, SECOND, MINUTE, HOUR, DAY, WEEK(begins on Sunday), ISOWEEK(begins on Monday), MONTH, QUARTER, YEAR, or ISOYEAR (See more [details](/acho-studio/data-prep-projects/applying-actions/tools/sql-editor/supported-math-functions-in-formula/timestamp-time-functions.md#details-about-date_part)) **Result Data Type** `TIMESTAMP` **Example** ``` TIMESTAMP_DIFF(TIMESTAMP("2020-12-25 13:00:00+00"), TIMESTAMP("2020-12-25 15:30:00+00"), HOUR) ``` Result: -2 ## TIMESTAMP\_TRUNC `TIMESTAMP_TRUNC(timestamp, date_part, [timezone])` **Description** Truncate a specific timestamp to the start of a given unit of duration and returns a new timestamp. **Input** * **`timestamp`**: a `TIMESTAMP` object or column * **`date_part`**: The unit of duration. It can be one of the following: MICROSECOND, MILLISECOND, SECOND, MINUTE, HOUR, DAY, WEEK, WEEK(), ISOWEEK, MONTH, QUARTER, YEAR or ISOYEAR (See more [details](/acho-studio/data-prep-projects/applying-actions/tools/sql-editor/supported-math-functions-in-formula/timestamp-time-functions.md#details-about-date_part)) * **`timezone`**: You can optionally specify a [time zone.](https://en.wikipedia.org/wiki/List_of_tz_database_time_zones) **Result Data Type** `TIMESTAMP` **Example** ``` TIMESTAMP_TRUNC(TIMESTAMP("2020-12-25 15:30:00+00"), DAY, "UTC") ``` Result: 2020-12-25 00:00:00 UTC ``` TIMESTAMP_TRUNC(TIMESTAMP("2020-12-25 15:30:00+00"), DAY, "America/Los_Angeles") ``` Result: 2020-12-25 08:00:00 UTC ## FORMAT\_TIMESTAMP `FORMAT_TIMESTAMP(format, timestamp, [timezone])` **Description** Transforms a timestamp variable into a string variable in a specific format. **Input** * **`format`**: an expected format of a timestamp. It is expressed in a `STRING`. To see supported format elements, visit [here](/acho-studio/data-prep-projects/applying-actions/tools/sql-editor/supported-math-functions-in-formula/timestamp-time-functions.md#supported-format-elements). * **`timestamp`**: a `TIMESTAMP` object or column * **`timezone`**: You can optionally specify a [time zone.](https://en.wikipedia.org/wiki/List_of_tz_database_time_zones) **Result Data Type** `STRING` **Example** ``` FORMAT_TIMESTAMP("%b-%d-%Y", TIMESTAMP "2020-12-25 15:30:00+00", "UTC") ``` Result: Dec-25-2020 ## PARSE\_TIMESTAMP `PARSE_TIMESTAMP(format, timestamp_expression, [timezone])` **Description** Convert a string to a timestamp variable according to the specified format. **Input** * **`format`**: a `STRING`object. To see supported format elements, visit [here](/acho-studio/data-prep-projects/applying-actions/tools/sql-editor/supported-math-functions-in-formula/timestamp-time-functions.md#supported-format-elements). * **`timestamp_expression`**: a `STRING` object or column * **`timezone`**: You can optionally specify a [time zone.](https://en.wikipedia.org/wiki/List_of_tz_database_time_zones) **Result Data Type** `TIMESTAMP` **Example** ``` PARSE_TIMESTAMP("%a %b %e %I:%M:%S %Y", "Thu Dec 25 07:30:00 2020") ``` Result: 2020-12-25 07:30:00 UTC ## TIMESTAMP\_SECONDS `TIMESTAMP_SECONDS(integer)` **Description** Convert a UNIX timestamp in seconds to a timestamp in a standard format. The UNIX timestamp in seconds is expressed as the number of seconds since 1970-01-01 00:00:00 UTC. **Result Data Type** `TIMESTAMP` **Example** ``` TIMESTAMP_SECONDS(1600219000) ``` Result: 2020-09-16 01:16:40 UTC ## TIMESTAMP\_MILLIS `TIMESTAMP_MILLIS(integer)` **Description** Convert a UNIX timestamp in milliseconds to a timestamp in a standard format. The UNIX timestamp in seconds is expressed as the number of milliseconds since 1970-01-01 00:00:00 UTC. **Result Data Type** `TIMESTAMP` **Example** ``` TIMESTAMP_MILLIS(1600219123456) ``` Result: 2020-09-16 01:18:43.456 UTC ## TIMESTAMP\_MICROS `TIMESTAMP_MICROS(integer)` **Description** Convert a UNIX timestamp in microseconds to a timestamp in a standard format. The UNIX timestamp in seconds is expressed as the number of microseconds since 1970-01-01 00:00:00 UTC. **Result Data Type** `TIMESTAMP` **Example** ``` TIMESTAMP_MICROS(1600219123456789) ``` Result: 2020-09-16 01:18:43.456789 UTC ## UNIX\_SECONDS `UNIX_SECONDS(timestamp)` **Description** Return the number of seconds since 1970-01-01 00:00:00 UTC. **Result Data Type** `INTEGER` **Example** ``` UNIX_SECONDS(TIMESTAMP("2008-12-25 15:30:00+00")) ``` Result: 1608910200 ## UNIX\_MILLIS `UNIX_MILLIS(timestamp)` **Description** Return the number of milliseconds since 1970-01-01 00:00:00 UTC. **Result Data Type** `INTEGER` **Example** ``` UNIX_MILLIS(TIMESTAMP("2020-12-25 15:30:00+00")) ``` Result: 1608910200000 ## UNIX\_MICROS `UNIX_MICROS(timestamp)` **Description** Return the number of microseconds since 1970-01-01 00:00:00 UTC. **Result Data Type** `INTEGER` **Example** ``` UNIX_MICROS(TIMESTAMP("2008-12-25 15:30:00+00")) ``` Result: 1608910200000000 ## Details about date\_part | Part | Description | | ----------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | MICROSECOND | The microsecond of the time. | | MILLISECOND | The millisecond of the time. | | SECOND | The second of the time. | | MINUTE | The minute of the time. | | HOUR | The hour of the time. | | DAYOFWEEK | The weekday from 1 to 7 with Sunday as the first day of the week. | | DAY | The day of the month. | | DAYOFYEAR | The day of the year. | | WEEK | The week number of the date from 0 to 53. (Weeks begin with Sunday, and dates prior to the first Sunday of the year are in week 0) | | WEEK(\): | The week number of the date from 0 to 53. (You can optionally specify the first weekday of a week and weeks begin with the weekday that you specified. Dates prior to the first \ of the year are in week 0. Valid values for \ are SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY, and SATURDAY.) | | ISOWEEK | Returns the ISO 8601 week number of the date from 1 to 53 with Monday as the first day of the week. (The first ISOWEEK of each ISO year begins on the Monday before the first Thursday of the Gregorian calendar year.) | | MONTH | Return the month of the date. | | QUARTER | Return values in the range between 1 and 4. | | YEAR | Return the month of the year. | | ISOYEAR | Return the ISO 8601 week-numbering year, which is the Gregorian calendar year containing the Thursday of the week to which date\_expression belongs. | | DATE | Return the `DATE` object in a format of "yyyy-mm-dd". | | DATETIME | Return the `DATETIME` object in a format of "yyyy-mm-dd HH:MM:SS". | | TIME | Return the `TIME` object in a format of "HH:MM:SS". | ## Supported Format Elements | Format element | Description | Example | | -------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | ------------------------------- | | %A | Return the full weekday name. | Tuesday | | %a | Return the abbreviated weekday name. | Tue | | %B | Return the full name of the month. | May | | %b or %h | Return the abbreviated name of the month. | May | | %C | Return the century | 20 | | %c | Return the date and time in a format of "%a %b %e %T %Y" | Tue May 4 15:31:13 2021 | | %D | Return the date in a format of "%m/%d/%y". | 05/04/21 | | %d | Return the day of the month. | 04 | | %e | Return the day of month without leading 0. | 4 | | %F | Return the date in a format of "%Y-%m-%d" | 2021-05-04 | | %G | Return the [ISO 8601](https://en.wikipedia.org/wiki/ISO_8601) year with the century. The ISO 8601 year begins on the Monday before the first Thursday of the Gregorian calendar year. | 2021 | | %g | Return the ISO 8601 year without the century. The ISO 8601 year begins on the Monday before the first Thursday of the Gregorian calendar year. | 21 | | %H | Return the hour in the 24-hour clock (00-23). | 15 | | %I | Return the hour in the 12-hour clock (01-12). | 03 | | %j | Return the day of the year (001-366). | 124 | | %k | Return the hour (0-23) in the 24-hour clock without leading 0. | 15 | | %l | Return the hour (1-12) in the 12-hour clock without leading 0 | 3 | | %M | Return the minute of the time (00-59). | 31 | | %m | Return the month of the date (01-12). | 05 | | %n | Return a newline character. | | | %P | Return a lowercase "am" or "pm". | pm | | %p | Return an uppercase "AM" or "PM". | PM | | %Q | Return the quarter of the date (1-4). | 2 | | %R | Return the time in a format of "%H:%M". | 15:31 | | %r | Return the time in the 12-hour clock with AM/PM notation. | 03:31:13 PM | | %S | Return the second of the time (00-60). | 13 | | %T | Return the time in a format of %H:%M:%S. | 15:31:13 | | %t | Return a tab character. | | | %U | Return the week number (00-53) of the year. Sunday is the first day of the week | 18 | | %u | Return the weekday of the date as 1-7. Monday is the first day of the week. | 2 | | %V | Return the ISO 8601 week number (01-53) of the year. Monday is the first day of the week. If the week containing January 1 has four or more days in the new year, then it returns "week 1". Otherwise, it returns "week 53" of the previous year, and considers the next week as "week 1". | 18 | | %W | Return the week number(00-53) of the year. Monday is the first day of the week. | 18 | | %w | Return the weekday of the date as 0-6. Sunday is the first day of the week. | 2 | | %X | Return the time in a format of "HH:MM:SS" | 15:31:13 | | %x | Return the date in a format of "MM/DD/YY" | 05/04/21 | | %Y | Return the year with the century | 2021 | | %y | Return the year (00-99) without the century with an optional leading zero. Can be mixed with %C. If %C is not specified, years 00-68 are the 2000s, while years 69-99 are the 1900s. | 21 | | %% | Return a single "%" character. | % | | %E#S | Return the seconds with the given number of digits of fractional precision. The "#" should be replaced by a number. |

"%E3S" returns
37.021

| | %E\*S | Return the seconds with full fractional precision. | 37.021851 | | %E4Y | Return the year in 4 digits (0000-9999). | 2021 | --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://docs.acho.io/acho-studio/data-prep-projects/applying-actions/tools/sql-editor/supported-math-functions-in-formula/timestamp-time-functions.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.