Timestamp Precision Rounding

Currently in Snowflake, it supports using the precision format of FF<N>, where N is a number between 0 to 9, to display precision part of the timestamp value. To demonstrate how it works, please see below examples:

SELECT TO_TIMESTAMP('2020-09-28 11:10:59.456789213');
+-----------------------------------------------+
| TO_TIMESTAMP('2020-09-28 11:10:59.456789213') |
|-----------------------------------------------|
| 2020-09-28 11:10:59.456                       |
+-----------------------------------------------+
1 Row(s) produced. Time Elapsed: 1.169s

By default the output returns only 3 digits, which is up to milliseconds, is because the session level parameter TIMESTAMP_OUTPUT_FORMAT has the default value of “YYYY-MM-DD HH24:MI:SS.FF3 TZHTZM”. To check the value in your setup, run below query:

SHOW PARAMETERS LIKE 'TIMESTAMP_OUTPUT_FORMAT';
+-------------------------+----------------------------------+----------------------------------+-------+-------------------------------------------------+--------+
| key                     | value                            | default                          | level | description                                     | type   |
|-------------------------+----------------------------------+----------------------------------+-------+-------------------------------------------------+--------|
| TIMESTAMP_OUTPUT_FORMAT | YYYY-MM-DD HH24:MI:SS.FF3 TZHTZM | YYYY-MM-DD HH24:MI:SS.FF3 TZHTZM |       | Default display format for all timestamp types. | STRING |
+-------------------------+----------------------------------+----------------------------------+-------+-------------------------------------------------+--------+
1 Row(s) produced. Time Elapsed: 1.192s

FF3 means only display 3 digits for the precision part, and TZHTZM means timezone offset hour based on UTC. The reason that the timezone info was missing from my first output was because the TIMESTAMP_TYPE_MAPPING maps TO_TIMESTAMP to TO_TIMESTAMP_NTZ, which is the non-timezone version of TO_TIMESTAMP function. Check the output of below query to confirm:

SHOW PARAMETERS LIKE 'TIMESTAMP_TYPE_MAPPING';
+------------------------+---------------+---------------+---------+----------------------------------------------------------------------------+--------+
| key                    | value         | default       | level   | description                                                                | type   |
|------------------------+---------------+---------------+---------+----------------------------------------------------------------------------+--------|
| TIMESTAMP_TYPE_MAPPING | TIMESTAMP_NTZ | TIMESTAMP_NTZ | ACCOUNT | If TIMESTAMP type is used, what specific TIMESTAMP* type it should map to: | STRING |
|                        |               |               |         |   TIMESTAMP_LTZ, TIMESTAMP_NTZ (default) or TIMESTAMP_TZ                   |        |
+------------------------+---------------+---------------+---------+----------------------------------------------------------------------------+--------+

So if I update the mapping to use the TO_TIMESTAMP_TZ version, the result will be different:

ALTER SESSION SET TIMESTAMP_TYPE_MAPPING = 'TIMESTAMP_TZ';
+----------------------------------+
| status                           |
|----------------------------------|
| Statement executed successfully. |
+----------------------------------+
1 Row(s) produced. Time Elapsed: 1.159s

SELECT TO_TIMESTAMP('2020-09-28 11:10:59.456789213');
+-----------------------------------------------+
| TO_TIMESTAMP('2020-09-28 11:10:59.456789213') |
|-----------------------------------------------|
| 2020-09-28 11:10:59.456 +0000                 |
+-----------------------------------------------+
1 Row(s) produced. Time Elapsed: 0.930s

You can see that the extra TZ info +0000 is added to the end.

Now, enough of some intro to the TO_TIMESTAMP function. The purpose of this post is to show you how to round the precision part of the timestamp, because currently Snowflake does not support rounding, but rather just truncate. See examples below:

ALTER SESSION SET TIMESTAMP_OUTPUT_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF1';
+----------------------------------+
| status                           |
|----------------------------------|
| Statement executed successfully. |
+----------------------------------+
1 Row(s) produced. Time Elapsed: 0.334s

SELECT TO_TIMESTAMP('2020-09-28 11:10:59.456789213');
+-----------------------------------------------+
| TO_TIMESTAMP('2020-09-28 11:10:59.456789213') |
|-----------------------------------------------|
| 2020-09-28 11:10:59.4                         |
+-----------------------------------------------+
1 Row(s) produced. Time Elapsed: 0.601s

============================================================

ALTER SESSION SET TIMESTAMP_OUTPUT_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF2';
+----------------------------------+
| status                           |
|----------------------------------|
| Statement executed successfully. |
+----------------------------------+
1 Row(s) produced. Time Elapsed: 0.371s

SELECT TO_TIMESTAMP('2020-09-28 11:10:59.456789213');
+-----------------------------------------------+
| TO_TIMESTAMP('2020-09-28 11:10:59.456789213') |
|-----------------------------------------------|
| 2020-09-28 11:10:59.45                        |
+-----------------------------------------------+
1 Row(s) produced. Time Elapsed: 0.674s

============================================================

ALTER SESSION SET TIMESTAMP_OUTPUT_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF8';
+----------------------------------+
| status                           |
|----------------------------------|
| Statement executed successfully. |
+----------------------------------+
1 Row(s) produced. Time Elapsed: 0.391s

SELECT TO_TIMESTAMP('2020-09-28 11:10:59.456789213');;
+-----------------------------------------------+
| TO_TIMESTAMP('2020-09-28 11:10:59.456789213') |
|-----------------------------------------------|
| 2020-09-28 11:10:59.45678921                  |
+-----------------------------------------------+
1 Row(s) produced. Time Elapsed: 0.493s

You can see that the values beyond the defined N value are truncated, not rounded, and Snowflake currently does not support precision rounding. To get the rounding effect, a custom UDF is needed.

I have built below UDF which I hope can be handy for anyone who might need it:

CREATE OR REPLACE FUNCTION to_timestamp_rounded(t TIMESTAMP, r INT)
  returns string
  as
  $$
    SELECT TO_DATE(CAST(t AS DATE)) || ' ' ||  TO_TIME(CAST(t AS TIME)) || '.' || CAST(ROUND(EXTRACT(ns, t) / 1000000000, r) * RPAD(1, r+1, 0) AS INT)
  $$
;

Then test the function:

SELECT to_timestamp_rounded('2020-09-28 11:10:59.456789213', 1);
+----------------------------------------------------------+
| TO_TIMESTAMP_ROUNDED('2020-09-28 11:10:59.456789213', 1) |
|----------------------------------------------------------|
| 2020-09-28 11:10:59.5                                    |
+----------------------------------------------------------+
1 Row(s) produced. Time Elapsed: 0.569s

SELECT to_timestamp_rounded('2020-09-28 11:10:59.456789213', 2);
+----------------------------------------------------------+
| TO_TIMESTAMP_ROUNDED('2020-09-28 11:10:59.456789213', 2) |
|----------------------------------------------------------|
| 2020-09-28 11:10:59.46                                   |
+----------------------------------------------------------+
1 Row(s) produced. Time Elapsed: 0.443s

SELECT to_timestamp_rounded('2020-09-28 11:10:59.456789213', 8);
+----------------------------------------------------------+
| TO_TIMESTAMP_ROUNDED('2020-09-28 11:10:59.456789213', 8) |
|----------------------------------------------------------|
| 2020-09-28 11:10:59.45678921                             |
+----------------------------------------------------------+
1 Row(s) produced. Time Elapsed: 0.469s

I hope above info can be useful.