SNOW-1761247: Support TIME and TIMESTAMP second-precision

[tvdboom]

What is the current behavior?

Currently, Snowpark's TimeType and TimestampType types are converted to Snowflake's time and timestamp types with the default 9 decimals (nanosecond) precision. There is no possibility to specify the precision like we can do in Snowflake.

What is the desired behavior?

Support second precision in these types. API would probably look like this TimeType(6).

How would this improve snowflake-snowpark-python?

It would make it easier to work with iceberg tables, since they only support microseconds precision.

[sfc-gh-sghosh]

Hello @tvdboom ,

Thanks for raising the issue.

You can convert the timestamp of precision 9 to 6 in snowpark using SQL to_timestamp and cast function.

Example

`from snowflake.snowpark import Session
from snowflake.snowpark.functions import col

data = [
('2024-11-07 12:03:54.817851234',),
('2024-01-01 12:00:00.123456879',)
]
schema = ["TIMESTAMP_COL"]

df = session.create_dataframe(data, schema)

-- Convert the timestamp column to timestamp with microsecond precision (6 decimals)
df_casted = df.with_column(
"TIMESTAMP_COL_MICROSECOND",
col("TIMESTAMP_COL").cast("timestamp") # Cast to timestamp (6) precision
)

df_casted.show()

Output:

|"TIMESTAMP_COL" |"TIMESTAMP_COL_MICROSECOND" |

|2024-11-07 12:03:54.817851234 |2024-11-07 12:03:54.817851 |
|2024-01-01 12:00:00.123456879 |2024-01-01 12:00:00.123456 |
`

[tvdboom]

Thanks for the answer. I assume this is happening because python's datetime object only supports up to microseconds. Am I to assume that snowpark doesn't support nanosecond precision? If I have a table in Snowflake with nanoseconds, is there a way I can get those values using Snowpark? I tried just using session.table(XX).limit(10).collect() but that returns objects of type datetime and thus loses the nanosecond information.