Snowflake TIMESTAMP - Preserve Millisecond Values to .000000, store in table

Question

I have a json value of 2045-06-02T09:23:41.8666668

I want to convert via TIMESTAMP to a DATE data type in snowflake AND hold the exact value, but I have three issues:

1) The TIMESTAMP data type is never stored in tables. (per https://docs.snowflake.net/manuals/sql-reference/data-types-datetime.html)

2) When I do use ::TIMESTAMP it cuts off at 2045-06-02 09:23:41.866

3)It removes the "T" that indicates time

Can anyone point me to documentation that handles this issue?

Simeon Pilgrim Simeon Pilgrim · Accepted Answer · 2019-11-21T03:40:13

1) & 2) The document (you linked to) says TIMESTAMP by default uses 9dp, what you are seeing when yor select is a presentation formatting issue, not a storage issues.

select '2045-06-02 09:23:41.8666668'::text as ta
    ,'2045-06-02 09:23:41.9777779'::text as tb
    ,ta::timestamp ta_d, ta::timestamp(0) ta_0
    ,ta::timestamp(3) ta_3
    ,ta::timestamp(6) ta_6
    ,tb::timestamp tb_d, tb::timestamp(0) tb_0
    ,tb::timestamp(3) tb_3, tb::timestamp(6) tb_6)
    ,datediff('millisecond', ta_d, tb_d )
    ,datediff('millisecond', ta_0, tb_0 )
    ,datediff('millisecond', ta_3, tb_3 )
    ,datediff('millisecond', ta_6, tb_6 ),
    datediff('microsecond', ta_d, tb_d )
    ,datediff('microsecond', ta_0, tb_0 )
    ,datediff('microsecond', ta_3, tb_3 )
    ,datediff('microsecond', ta_6, tb_6 )
    ,datediff('nanosecond', ta_d, tb_d )
    ,datediff('nanosecond', ta_0, tb_0 )
    ,datediff('nanosecond', ta_3, tb_3 )
    ,datediff('nanosecond', ta_6, tb_6 )
    ;

shows by default you are getting 9 decimal places.

3) that is a formatting thing..

Snowflake TIMESTAMP - Preserve Millisecond Values to .000000, store in table

1 Answers