---
title: Time Zone Functions
slug: -ScJIoRjBmaDA3w3JnZq9
description: Utilize time zone functions in Ocient to handle global data and time zone adjustments accurately.
docTags: 
createdAt: Wed Jan 18 2023 17:54:38 GMT+0000 (Coordinated Universal Time)
updatedAt: Mon Mar 30 2026 18:52:46 GMT+0000 (Coordinated Universal Time)
---

:::hint{type="warning"}
The Ocient® cluster system administrator is responsible for keeping the OS time zone database (e.g., on Ubuntu®, `tzdata`) up to date. Outdated time zone database files might cause unexpected results due to IANA rule updates around name changes, DST observance, and UTC offsets for time zones.
:::

:::hint{type="info"}
The `TIMESTAMP` data type has no associated time zone value. The database administrator tracks the associated time zone with a `TIMESTAMP` column, whether through an associated `time_zone` `VARCHAR` column or other documentation. Ocient recommends storing all `TIMESTAMP` data types in the UTC time zone to avoid ambiguity and using these functions to convert to arbitrary time zones. You can still store `TIMESTAMP` data types associated with an arbitrary time zone and use these functions to convert to UTC and from UTC to another time zone.&#x20;
:::

# CONVERT\_UTC\_TIMESTAMP\_TO\_LOCAL

The function converts a timestamp from the UTC time zone to a specified local time zone.&#x20;

This function can take the `timezone` argument as a `VARCHAR` literal, or the argument can be a column. When the argument is a literal, you can arbitrarily capitalize it. When the argument is a column, the values in the column must be capitalized according to the canonical [IANA time zone names,](https://www.iana.org/time-zones) such as `'America/Chicago'`.

## **Syntax**

```sql
SELECT CONVERT_UTC_TIMESTAMP_TO_LOCAL(timestamp,timezone)
```

| **Argument** | **Data** **Type**        | **Description**                                                                                              |
| ------------ | ------------------------ | ------------------------------------------------------------------------------------------------------------ |
| `timestamp`  | `TIMESTAMP` or `VARCHAR` | Timestamp to convert that you specify as a `TIMESTAMP` or `VARCHAR` that you typecast in a timestamp format. |
| `timezone`   | `VARCHAR`                | `VARCHAR` that contains the full name of the target time zone.                                               |

## **Examples**

### Convert a Constant Time Zone Value

Convert timestamp `2022-11-06 02:09:00` to the local time in Chicago.

```sql
SELECT CONVERT_UTC_TIMESTAMP_TO_LOCAL(Timestamp '2022-11-06 02:09:00','America/Chicago');
```

*Output*

:::BlockQuote
CONVERT\_UTC\_TIMESTAMP\_TO\_LOCAL(timestamp(('2022-11-06 02:09:00')), ('America/Chicago'))
\------------------------------------------------------------------------------------
2022-11-05 21:09:00.000000000
:::

### Convert a Timestamp Value in a Column

Convert the timestamp in the `ts` column to the local time in Chicago using the `test_timezone` table.

```sql
SELECT CONVERT_UTC_TIMESTAMP_TO_LOCAL(ts,'America/Chicago') FROM test_timezone;
```

*Output*

:::BlockQuote
CONVERT\_UTC\_TIMESTAMP\_TO\_LOCAL(ts, ('America/Chicago'))
\-----------------------------------------------------
2023-01-09 17:23:12.173997444
:::

### Convert a Timestamp Value and Time Zone Name in Columns

Convert the timestamp in the `ts` column to the local time specified by the `timezone_name` column using the `test_timezone` table.

```sql
SELECT CONVERT_UTC_TIMESTAMP_TO_LOCAL(ts,timezone_name) FROM test_timezone;
```

*Output*

:::BlockQuote
CONVERT\_UTC\_TIMESTAMP\_TO\_LOCAL(ts, timezone\_name)
\-----------------------------------------------------
2023-01-09 17:43:21.193229991
:::

### Convert a Timestamp Value Using Other Date and Time Functions

Use the `TO_TIMESTAMP` function to convert a numeric timestamp to the local time in Chicago.

```sql
SELECT CONVERT_UTC_TIMESTAMP_TO_LOCAL(TO_TIMESTAMP(1534587890), 'America/Chicago');
```

*Output*

:::BlockQuote
CONVERT\_UTC\_TIMESTAMP\_TO\_LOCAL(to\_timestamp((1534587890)), ('America/Chicago'))
\-----------------------------------------------
2018-08-18 05:24:50.000000000
:::

Use a UTC timestamp from the output of the `CONVERT_LOCAL_TIMESTAMP_TO_UTC` function with Chicago time and convert it back to local time in Chicago.

```sql
SELECT CONVERT_UTC_TIMESTAMP_TO_LOCAL(CONVERT_LOCAL_TIMESTAMP_TO_UTC(Timestamp '2022-11-09 01:59:00', 'America/Chicago'),
    'America/Chicago');
```

*Output*

:::BlockQuote
CONVERT\_UTC\_TIMESTAMP\_TO\_LOCAL(CONVERT\_LOCAL\_TIMESTAMP\_TO\_UTC(timestamp(('2022-11-09 01:59:00')), ('America/Chicago')), ('America/Chicago'))
\------------------------------------------------------------------------------------
2022-11-09 01:59:00.000000000
:::

Extract the hour portion of the local Chicago time using the output of the `TO_TIMESTAMP` function on a numeric timestamp.

```sql
SELECT HOUR(CONVERT_UTC_TIMESTAMP_TO_LOCAL(TO_TIMESTAMP(1534587890), 'America/Chicago'));
```

*Output*

:::BlockQuote
hour(CONVERT\_UTC\_TIMESTAMP\_TO\_LOCAL(to\_timestamp((1534587890)), ('America/Chicago')))
\----------------------------------------------------------------------------------
5
:::

### Convert a Timestamp During Table Creation

Create a table using a `SELECT` statement that converts a timestamp in local Chicago time.

```sql
CREATE TABLE test_timezone_2 (ts timestamp, b int) 
    AS (SELECT CONVERT_UTC_TIMESTAMP_TO_LOCAL(Timestamp '2022-11-06 02:09:00', 'America/Chicago'), 
        1 FROM sys.dummy1);

Modified 1 row

SELECT * FROM test_timezone_2;            1
```

*Output*

:::BlockQuote
ts\_1                                       b\_1
\------------------------------------------------------------------------------------
2022-11-05 21:09:00.000000000
:::

### Convert a Timestamp During an Insert Operation

Insert a timestamp in local Chicago time into the `test_timezone_2` table.

```sql
INSERT INTO test_timezone_2 SELECT CONVERT_UTC_TIMESTAMP_TO_LOCAL(Timestamp '2022-11-06 02:09:00', 'America/Chicago'), 
    1 FROM sys.dummy1;
    
Modified 1 row
```

### Convert a Timestamp in a Query Filter

Use timestamp, a numeric constant value in local Chicago time, as a filter on the column `ts` in the `test_timezone` table.

```sql
SELECT * FROM test_timezone 
    WHERE ts=CONVERT_UTC_TIMESTAMP_TO_LOCAL(Timestamp '2022-11-06 14:09:00', 'America/Chicago');
```

*Output*

:::BlockQuote
ts                                          b          
\--------------------------------------------------------
2022-11-06 08:09:00.000000000               1
:::

Use timestamp, a runtime value from the table column `ts` in local Chicago time, as a filter on the `test_timezone` table. &#x20;

```sql
SELECT * FROM test_timezone 
    WHERE CONVERT_UTC_TIMESTAMP_TO_LOCAL(ts::timestamp, 'America/Chicago')='2022-11-06 08:09:00.000000000';
```

*Output*

:::BlockQuote
ts                                          b          
\--------------------------------------------------------
2022-11-06 08:09:00.000000000               1
:::

### Convert a Timestamp in the Grouping Part of the Query

Group the query results by a timestamp converted to local time in Chicago.

```sql
SELECT CONVERT_UTC_TIMESTAMP_TO_LOCAL(ts::timestamp, 'America/Chicago') 
    FROM test_timezone GROUP BY CONVERT_UTC_TIMESTAMP_TO_LOCAL(ts::timestamp, 'America/Chicago');
```

*Output*

:::BlockQuote
CONVERT\_UTC\_TIMESTAMP\_TO\_LOCAL(ts, ('America/Chicago'))
\-----------------------------------------------------
2022-11-05 04:09:00.000000000
2022-11-09 02:09:00.000000000
2022-11-04 04:09:00.000000000
2022-11-06 02:09:00.000000000
:::

Using a grouping, omit the `2022-11-10 02:09:00` timestamp using the `HAVING` clause.

```sql
SELECT CONVERT_UTC_TIMESTAMP_TO_LOCAL(ts::timestamp, 'America/Chicago') 
    FROM test_timezone GROUP BY CONVERT_UTC_TIMESTAMP_TO_LOCAL(ts::timestamp, 'America/Chicago') 
    HAVING CONVERT_UTC_TIMESTAMP_TO_LOCAL(ts::timestamp, 'America/Chicago') != '2022-11-10 02:09:00';
```

*Output*

:::BlockQuote
CONVERT\_UTC\_TIMESTAMP\_TO\_LOCAL(ts, ('America/Chicago'))
\-----------------------------------------------------
2022-11-06 02:09:00.000000000
2022-11-04 04:09:00.000000000
2022-11-05 04:09:00.000000000
:::

# CONVERT\_LOCAL\_TIMESTAMP\_TO\_UTC

The function converts a timestamp in a specified local time zone to the UTC time zone. In the case of ambiguous timestamps, the database returns the earliest of the two possible times in the UTC time zone. Ambiguous timestamps refer to the times when the DST time zone change happens, and 1 hour can transform to either 1 or +1 and 1 or -1. Ambiguous timestamps can also refer to non-existent times, such as `2023-03-12 02:01:00 'America/New_York'`, which are similarly converted to the earliest possible UTC time,  `2023-03-12 07:00:00 'UTC'`, due to the skipping of hour `02:00:00` entirely with DST spring forward.

This function can take the `timezone` argument as a `VARCHAR` literal, or the argument can be a column. When the argument is a literal, you can arbitrarily capitalize it. When the argument is a column, the values in the column must be capitalized according to the canonical IANA time zone names, such as `'America/Chicago'`.

## **Syntax**

```sql
SELECT CONVERT_LOCAL_TIMESTAMP_TO_UTC(timestamp,timezone)
```

| **Argument** | **Data** **Type**        | **Description**                                                                                              |
| ------------ | ------------------------ | ------------------------------------------------------------------------------------------------------------ |
| `timestamp`  | `TIMESTAMP` or `VARCHAR` | Timestamp to convert that you specify as a `TIMESTAMP` or `VARCHAR` that you typecast in a timestamp format. |
| `timezone`   | `VARCHAR`                | `VARCHAR` that contains the full name of the target time zone.                                               |

## **Examples**

### Convert a Constant Time Zone Value

Convert timestamp `2022-11-06 02:09:00` to Chicago UTC time.

```sql
SELECT CONVERT_LOCAL_TIMESTAMP_TO_UTC(Timestamp '2022-11-06 02:09:00','America/Chicago');
```

*Output*

:::BlockQuote
CONVERT\_LOCAL\_TIMESTAMP\_TO\_UTC(timestamp(('2022-11-06 02:09:00')), ('America/Chicago'))
\------------------------------------------------------------------------------------
2022-11-06 08:09:00.000000000
:::

### Convert a Timestamp Value in a Column

Convert the timestamp in the `ts` column to Chicago UTC time using the `test_timezone` table.

```sql
SELECT CONVERT_LOCAL_TIMESTAMP_TO_UTC(ts,'America/Chicago') FROM test_timezone;
```

*Output*

:::BlockQuote
CONVERT\_LOCAL\_TIMESTAMP\_TO\_UTC(ts, ('America/Chicago'))
\-----------------------------------------------------
2023-01-09 17:23:12.173997444
:::

### Convert a Timestamp Value and Time Zone Name in Columns

Convert the timestamp in the `ts` column to the UTC time specified by the `timezone_name` column using the `test_timezone` table.

```sql
SELECT CONVERT_LOCAL_TIMESTAMP_TO_UTC(ts,timezone_name) FROM test_timezone;
```

*Output*

:::BlockQuote
CONVERT\_LOCAL\_TIMESTAMP\_TO\_UTC(ts, timezone\_name)
\-----------------------------------------------
2023-01-09 17:43:21.193229991
:::

### Convert a Timestamp Value Using Other Date and Time Functions

Use the `TO_TIMESTAMP` function to convert a numeric timestamp to the Chicago UTC time.

```sql
SELECT CONVERT_LOCAL_TIMESTAMP_TO_UTC(TO_TIMESTAMP(1534587890), 'America/Chicago');
```

*Output*

:::BlockQuote
CONVERT\_LOCAL\_TIMESTAMP\_TO\_UTC(to\_timestamp((1534587890)), ('America/Chicago'))
\-----------------------------------------------
2018-08-18 15:24:50.000000000
:::

Use a local timestamp from the output of the `CONVERT_UTC_TIMESTAMP_TO_LOCAL` function with Chicago time and convert it back to UTC time.

```sql
SELECT CONVERT_LOCAL_TIMESTAMP_TO_UTC(TO_TIMESTAMP(CONVERT_UTC_TIMESTAMP_TO_LOCAL(Timestamp '2022-11-06 01:59:00', 'America/Chicago'),
    'America/Chicago');
```

*Output*

:::BlockQuote
CONVERT\_LOCAL\_TIMESTAMP\_TO\_UTC(CONVERT\_UTC\_TIMESTAMP\_TO\_LOCAL(timestamp(('2022-11-06 01:59:00')), ('America/Chicago')), ('America/Chicago'))
\------------------------------------------------------------------------------------
2022-11-06 01:59:00.000000000
:::

### Convert a Timestamp During Table Creation

Create a table using a `SELECT` statement that converts a timestamp in Chicago UTC time.

```sql
CREATE TABLE test_timezone (ts timestamp, b int) 
    AS (SELECT CONVERT_LOCAL_TIMESTAMP_TO_UTC(Timestamp '2022-11-09 02:09:00', 'America/Chicago'), 
    1 FROM sys.dummy1);

Modified 1 row

SELECT * FROM test_timezone;
```

*Output*

:::BlockQuote
ts                                          b             
\---------------------------------------------------
2022-11-09 08:09:00.000000000               1         &#x20;
:::

### Convert a Timestamp During an Insert Operation

Insert a timestamp in Chicago UTC time into the `test_timezone` table.

```sql
INSERT INTO test_timezone SELECT CONVERT_LOCAL_TIMESTAMP_TO_UTC(Timestamp '2022-11-06 02:09:00', 'America/Chicago'), 
    1 from sys.dummy1;
    
Modified 1 row
```

### Convert a Timestamp in a Query Filter

Use a UTC timestamp in Chicago as a numeric constant value to filter on the column `ts` in the `test_timezone` table.

```sql
SELECT * FROM test_timezone 
    WHERE ts=CONVERT_LOCAL_TIMESTAMP_TO_UTC(Timestamp '2022-11-06 02:09:00', 'America/Chicago');
```

*Output*

:::BlockQuote
ts                                          b          
\--------------------------------------------------------
2022-11-06 08:09:00.000000000               1
:::

Use a timestamp in Chicago UTC time, a runtime value from the table column `ts`, as a filter on the `test_timezone` table. &#x20;

```sql
SELECT * FROM test_timezone 
    WHERE CONVERT_LOCAL_TIMESTAMP_TO_UTC(ts::timestamp, 'America/Chicago')='2022-11-06 08:09:00.000000000';
```

*Output*

:::BlockQuote
ts                                          b          
\--------------------------------------------------------
2022-11-06 08:09:00.000000000               1
:::

### Convert a Timestamp in the Grouping Part of the Query

Group the query results by a timestamp converted to UTC time in Chicago.

```sql
SELECT CONVERT_LOCAL_TIMESTAMP_TO_UTC(ts::timestamp, 'America/Chicago') 
    FROM test_timezone GROUP BY CONVERT_LOCAL_TIMESTAMP_TO_UTC(ts::timestamp, 'America/Chicago');
```

*Output*

:::BlockQuote
CONVERT\_LOCAL\_TIMESTAMP\_TO\_UTC(ts, ('America/Chicago'))
\-----------------------------------------------------
2022-11-09 14:09:00.000000000
2022-11-06 14:09:00.000000000
2022-11-04 14:09:00.000000000
2022-11-05 14:09:00.000000000
:::

Using a grouping, omit the `2022-11-05 14:09:00` timestamp using the `HAVING` clause.

```sql
SELECT CONVERT_LOCAL_TIMESTAMP_TO_UTC(ts::timestamp, 'America/Chicago') 
    FROM test_timezone GROUP BY CONVERT_LOCAL_TIMESTAMP_TO_UTC(ts::timestamp, 'America/Chicago') 
    HAVING CONVERT_LOCAL_TIMESTAMP_TO_UTC(ts::timestamp, 'America/Chicago') != '2022-11-05 14:09:00';
```

*Output*

:::BlockQuote
CONVERT\_LOCAL\_TIMESTAMP\_TO\_UTC(ts, ('America/Chicago'))
\-----------------------------------------------------
2022-11-09 14:09:00.000000000
2022-11-06 14:09:00.000000000
2022-11-04 14:09:00.000000000
:::

## Related Links

[General SQL Syntax](<././Query Ocient/General SQL Syntax.md>)

[Date and Time Functions](<./Date and Time Functions.md>)

[Conversion Functions](<./Geospatial Functions/Conversion Functions.md>)

[CREATE TABLE](<./Data Definition Language _DDL_ Statement Reference/Tables.md>)&#x20;

[INSERT INTO TABLE](<./Data Definition Language _DDL_ Statement Reference/Tables.md>)&#x20;

