> ## Documentation Index
> Fetch the complete documentation index at: https://docs.firebolt.io/llms.txt
> Use this file to discover all available pages before exploring further.

<AgentInstructions>

## Submitting Feedback

If you encounter incorrect, outdated, or confusing documentation on this page, submit feedback:

POST https://docs.firebolt.io/feedback

```json
{
  "path": "/reference-sql/functions-reference/date-and-time/to-timestamp",
  "feedback": "Description of the issue"
}
```

Only submit feedback when you have something specific and actionable to report.

</AgentInstructions>

> Reference material for TO_TIMESTAMP function

# TO_TIMESTAMP

export const QueryWindow = ({content}) => {
  const {sql, result} = content;
  const [inited, setInited] = useState(false);
  const buttonRef = useRef(null);
  useEffect(() => {
    if (!inited && buttonRef.current) {
      runQuery(buttonRef.current, true);
      setInited(true);
    }
  }, []);
  return <div className="query-window">
      <div className="query-toolbar">
        <button className="run-button" onClick={ev => runQuery(ev.target)} ref={buttonRef}>Run Query</button>
        <span className="window-title">Interactive SQL Playground 🔥</span>
      </div>
      <div className="query-content">
        <pre><code className="firebolt-sql language-sql" contentEditable="true" spellCheck="false" data-original-query={sql}>{sql}</code></pre>
        <script type="application/json" className="fallback-result" style={{
    display: "none"
  }}>{JSON.stringify(result)}</script>
        <div className="server-unavailable-banner query-window-hidden">
          The Firebolt playground server is currently unavailable. Using precomputed query results.
        </div>
        <div className="query-results"></div>
      </div>
    </div>;
};

See [below](#formatting-function) for the formatting function that converts from a formatted string to `TIMESTAMPTZ`.

**Alias:** `FROM_UNIXTIME` - only accepts one parameter of type `DOUBLE PRECISION`

## Conversion function

Converts the number of seconds since the Unix epoch (`1970-01-01 00:00:00 UTC`) to a `TIMESTAMPTZ` value.

### Syntax

`TO_TIMESTAMP(<seconds>)`

### Parameters

| Parameter   | Description                                                                                                                                                                    |
| :---------- | :----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `<seconds>` | A value expression of type `DOUBLE PRECISION` representing the number of seconds before or after the Unix epoch. If present, the fractional part is interpreted as subseconds. |

### Return Type

`TIMESTAMPTZ`

### Remarks

`TO_TIMESTAMP(<seconds>)` is the inverse function of `EXTRACT(EPOCH FROM TIMESTAMPTZ)`.

### Example

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SET time_zone = 'Europe/Berlin';
SELECT TO_TIMESTAMP(42.123456::DOUBLE PRECISION);  --> 1970-01-01 01:00:42.123456+01
```

## Formatting function

Converts a string to `TIMESTAMPTZ` type (i.e., timestamp with time zone) using format.

### Syntax

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
TO_TIMESTAMP(<expression> [,'<format>'])
```

### Parameters

| Parameter      | Description                                                                                                                                                                                                                                                                                                                                                    | Supported input types |
| :------------- | :------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | :-------------------- |
| `<expression>` | The text to convert to a timestamp with time zone. If no optional `<format>` argument is given that can be used to parse the `<expression>`, the following format is required: any supported date format directly followed by `( \|T)[H]H:[m]m:[S]S[.F]`. (For supported date formats see [TO\_DATE](/reference-sql/functions-reference/date-and-time/to-date) | `TEXT`                |
| `<format>`     | Optional. A string literal that specifies the format of the `<expression>` to convert.                                                                                                                                                                                                                                                                         | `TEXT` (see below)    |

Accepted `<format>` patterns include the following specifications:

| Format option    | Description                                        | Example                                                              |
| :--------------- | :------------------------------------------------- | :------------------------------------------------------------------- |
| `YYYY`           | Year (4 or more digits)                            | `TO_TIMESTAMP('2023', 'YYYY'); --> '2023-01-01 00:00:00+00'`         |
| `YYY`            | Last 3 digits of year                              | `TO_TIMESTAMP('2023', 'YYY'); --> '2023-01-01 00:00:00+00'`          |
| `YY`             | Last 2 digits of year                              | `TO_TIMESTAMP('2023', 'YY'); --> '2023-01-01 00:00:00+00'`           |
| `Y`              | Last digit of year                                 | `TO_TIMESTAMP('2023', 'Y'); --> '2023-01-01 00:00:00+00'`            |
| `MONTH`          | Full month name (case insensitive)                 | `TO_TIMESTAMP('august', 'MONTH'); --> '0001-08-01 00:00:00+00'`      |
| `MON`            | abbreviated month name (3 chars, case insensitive) | `TO_TIMESTAMP('dec', 'MON'); --> '0001-12-01 00:00:00+00'`           |
| `MM`             | Month number (01–12)                               | `TO_TIMESTAMP('7', 'MM'); --> '0001-07-01 00:00:00+00'`              |
| `DD`             | Day of month (01–31)                               | `TO_TIMESTAMP('15', 'DD'); --> '0001-01-15 00:00:00+00'`             |
| `HH` or `HH12`   | Hour of day (01–12)                                | `TO_TIMESTAMP('8', 'hh'); --> '0001-01-01 08:00:00+00'`              |
| `HH24`           | Hour of day (00–23)                                | `TO_TIMESTAMP('18', 'hh24'); --> '0001-01-01 18:00:00+00'`           |
| `MI`             | Minute (00–59)                                     | `TO_TIMESTAMP('35', 'hh'); --> '0001-01-01 00:35:00+00'`             |
| `SS`             | Second (00–59)                                     | `TO_TIMESTAMP('52', 'hh'); --> '0001-01-01 00:00:52+00'`             |
| `MS`             | Millisecond (000–999)                              | `TO_TIMESTAMP('89', 'ms'); --> '0001-01-01 08:00:00.89+00'`          |
| `US`             | Microsecond (000000–999999)                        | `TO_TIMESTAMP('04852', 'hh'); --> '0001-01-01 08:00:00.04852+00'`    |
| `AM` or `PM `    | meridiem indicator (without periods)               | `TO_TIMESTAMP('5 am', 'hh PM'); --> '0001-01-01 05:00:00+00+00'`     |
| `A.M.` or `P.M.` | meridiem indicator (with periods)                  | `TO_TIMESTAMP('5 p.m.', 'hh A.M.'); --> '0001-01-01 17:00:00+00'`    |
| `TZH`            | Time zone hours                                    | `TO_TIMESTAMP('12 -2', 'hh24 TZH'); --> '0001-01-01 14:00:00+00+00'` |
| `TZM`            | Time zone minutes                                  | `TO_TIMESTAMP('12 45', 'hh24 TZM'); --> '0001-01-01 11:15:00+00`     |

**Usage notes for formatting**

* Case letters in the input `<expression>` are ignored
* A separator (non-digit and non-letter) in the `<format>` string will match exactly one separator or is skipped
* Any non-separator in the `<format>` that is not part of a format option will match exactly one other character.
* Any character in quotes `"` will match exactly one other character.
* If the year format specification is `'YYY'`, `'YY'`, or `'Y'` and the supplied year is less than four digits, the year will be adjusted to be nearest to the year 2020, (e.g., `80` becomes `1980`).
* Milliseconds and microseconds are interpreted as subseconds in the form `ss.xxx[xxx]`. This means that `TO_TIMESTAMP('30.7', 'SS.MS')` is not 7 milliseconds, but 700, because it will be treated as 30 + 0.7 seconds. To achieve 7 milliseconds, one ust write `30.007` instead.
* Modifiers (e.g., `'FM'`) are not supported.

Some additional format patterns are reserved but currently not implemented: `FF1`, `FF2`, `FF3`, `FF4`, `FF5`, `FF6`, `SSSS`, `SSSSS`, `IYYY`, `IYY`, `IY`, `I`, `BC`, `AD`, `B_DOT_C_DOT`, `A_DOT_D_DOT`, `DAY`, `DY`, `DDD`, `IDDD`, `D`, `ID`, `W`, `WW`, `IW`, `CC`, `J`, `Q`.
Using them in the format string raises an error.

### Examples

The example below shows our separators and non-separators can cause skips. The separator `' '` (space) in the `<format>` matches the other separator `'/'` in the `<expression>`.
The non-separator `'x'` will match any other character, in this case the `'a'`. Lastly, the two separators `'++'` will match up to two other separators,
here the first `'x'` matches `'.'` while the second `'x'` will simply be ignored as no other separators follow.

<QueryWindow
  content={{
"sql": "SELECT\n    TO_TIMESTAMP(\n        '2023/aJUN.23',\n        'YYYY xMON++DD'\n    ) as ts;",
"result": {
"data": [
  [
    "2023-06-23 00:00:00+00"
  ]
],
"meta": [
  {
    "name": "ts",
    "type": "timestamptz"
  }
],
"query": {
  "query_id": "9e09cd6f-094c-4afa-a945-3b577bbcfe56",
  "query_label": null,
  "request_id": "4f26544f-c82c-454b-b1b7-d241c21ce3dc"
},
"rows": 1,
"statistics": {
  "bytes_read": 1,
  "elapsed": 0.006436,
  "rows_read": 1,
  "scanned_bytes_cache": 0,
  "scanned_bytes_storage": 0,
  "time_before_execution": 0.000241597,
  "time_to_execute": 8.5135e-05
}
}
}}
/>

The example below shows how the year is adjusted to be nearest to 2020 because `YYY` was used to match a less than four digit number. To receive the exact year `'180'` use `YYYY` instead.
Furthermore, as the three separators are quotes `"..."` they will match any character (separator or non-separator) which in this case is `'ar '`.

<QueryWindow
  content={{
"sql": "SELECT\n    TO_TIMESTAMP(\n        'Year 180: August 4th',\n        'xx\"...\"yyy: month DDxx'\n    ) as ts;",
"result": {
"data": [
  [
    "2180-08-04 00:00:00+00"
  ]
],
"meta": [
  {
    "name": "ts",
    "type": "timestamptz"
  }
],
"query": {
  "query_id": "ef5a0f5d-5939-4cde-b63e-ac0cac27614b",
  "query_label": null,
  "request_id": "666cdffa-faf9-4269-b443-508417d3f8eb"
},
"rows": 1,
"statistics": {
  "bytes_read": 1,
  "elapsed": 0.006966,
  "rows_read": 1,
  "scanned_bytes_cache": 0,
  "scanned_bytes_storage": 0,
  "time_before_execution": 0.000259797,
  "time_to_execute": 0.000113453
}
}
}}
/>

<QueryWindow
  content={{
"sql": "SELECT\n    TO_TIMESTAMP(\n        'Date: August 2nd, 2023 at 3pm +2',\n        'Xxxx: month DDxx, YYYY at HH12am TZH'\n    ) as ts;",
"result": {
"data": [
  [
    "2023-08-02 13:00:00+00"
  ]
],
"meta": [
  {
    "name": "ts",
    "type": "timestamptz"
  }
],
"query": {
  "query_id": "d36a89c0-4b57-4c21-99f0-127d1159cfc5",
  "query_label": null,
  "request_id": "cf43ac13-fc89-4172-9b3b-6c41f15d2349"
},
"rows": 1,
"statistics": {
  "bytes_read": 1,
  "elapsed": 0.007017,
  "rows_read": 1,
  "scanned_bytes_cache": 0,
  "scanned_bytes_storage": 0,
  "time_before_execution": 0.000321743,
  "time_to_execute": 0.000104886
}
}
}}
/>

<QueryWindow
  content={{
"sql": "SELECT\n    TO_TIMESTAMP(\n        'h:19 m:34 s:29 ms:035 us:000123',\n        'X:hh24 X:mi X:ss XX:ms XX:us'\n    ) as ts;",
"result": {
"data": [
  [
    "0001-01-01 19:34:29.035123+00"
  ]
],
"meta": [
  {
    "name": "ts",
    "type": "timestamptz"
  }
],
"query": {
  "query_id": "1f3146c7-564b-44c8-9cf5-29df6cddffea",
  "query_label": null,
  "request_id": "d0dbaba8-c1d6-4737-83d2-67775e4d9665"
},
"rows": 1,
"statistics": {
  "bytes_read": 1,
  "elapsed": 0.00662,
  "rows_read": 1,
  "scanned_bytes_cache": 0,
  "scanned_bytes_storage": 0,
  "time_before_execution": 0.000243947,
  "time_to_execute": 9.1721e-05
}
}
}}
/>

<QueryWindow
  content={{
"sql": "SELECT\n    FROM_UNIXTIME(\n        1728053781.761451\n    ) as ts;",
"result": {
"data": [
  [
    "2024-10-04 14:56:21.761451+00"
  ]
],
"meta": [
  {
    "name": "ts",
    "type": "timestamptz"
  }
],
"query": {
  "query_id": "6b1bb731-5d4b-45d8-be31-26e75194cbe7",
  "query_label": null,
  "request_id": "fe498068-ec61-4f15-b5da-4593e5f8b272"
},
"rows": 1,
"statistics": {
  "bytes_read": 1,
  "elapsed": 0.042488,
  "rows_read": 1,
  "scanned_bytes_cache": 0,
  "scanned_bytes_storage": 0,
  "time_before_execution": 0.000267754,
  "time_to_execute": 0.000118175
}
}
}}
/>

Due to rounding it can happen that the conversion from an extracted epoch back to a timestamp does not generate the original timestamp.

<QueryWindow
  content={{
"sql": "WITH data as (\n    select '1597-12-03 13:49:30.511757+00'::timestamptz as ts \n)\nSELECT\n    ts as original_ts,\n    FROM_UNIXTIME(\n        extract(epoch from ts)\n    ) as converted_ts\nFROM data",
"result": {
"data": [
  [
    "1597-12-03 13:49:30.511757+00",
    "1597-12-03 13:49:30.511756+00"
  ]
],
"meta": [
  {
    "name": "original_ts",
    "type": "timestamptz"
  },
  {
    "name": "converted_ts",
    "type": "timestamptz"
  }
],
"query": {
  "query_id": "227289eb-c20f-4721-8832-8d0bc652ff34",
  "query_label": null,
  "request_id": "d391fd52-a946-4aef-897f-3e06961bb6a4"
},
"rows": 1,
"statistics": {
  "bytes_read": 1,
  "elapsed": 0.009392,
  "rows_read": 1,
  "scanned_bytes_cache": 0,
  "scanned_bytes_storage": 0,
  "time_before_execution": 0.000273329,
  "time_to_execute": 0.00010073
}
}
}}
/>