snowflake.snowpark.functions.from_unixtime¶

snowflake.snowpark.functions.from_unixtime(e: Union[Column, str], fmt: Optional[Column] = None) → Column[source]¶

Converts an input expression into the corresponding timestamp.

Per default fmt is set to auto, which makes Snowflake detect the format automatically. With to_timestamp strings can be converted to timestamps. The format has to be specified according to the rules set forth in <https://docs.snowflake.com/en/sql-reference/functions-conversion#date-and-time-formats-in-conversion-functions>

Example::
>>> df = session.create_dataframe(['2019-01-31 01:02:03.004'], schema=['a'])
>>> df.select(to_timestamp(col("a")).as_("ans")).collect()
[Row(ANS=datetime.datetime(2019, 1, 31, 1, 2, 3, 4000))]
>>> df = session.create_dataframe(["2020-05-01 13:11:20.000"], schema=['a'])
>>> df.select(to_timestamp(col("a"), lit("YYYY-MM-DD HH24:MI:SS.FF3")).as_("ans")).collect()
[Row(ANS=datetime.datetime(2020, 5, 1, 13, 11, 20))]
Copy

Another option is to convert dates into timestamps

Example::
>>> import datetime
>>> df = session.createDataFrame([datetime.datetime(2022, 12, 25, 13, 59, 38, 467)], schema=["a"])
>>> df.select(to_timestamp(col("a"))).collect()
[Row(TO_TIMESTAMP("A")=datetime.datetime(2022, 12, 25, 13, 59, 38, 467))]
>>> df = session.createDataFrame([datetime.date(2023, 3, 1)], schema=["a"])
>>> df.select(to_timestamp(col("a"))).collect()
[Row(TO_TIMESTAMP("A")=datetime.datetime(2023, 3, 1, 0, 0))]
Copy

Integers can be converted into a timestamp as well, by providing optionally a scale as an integer as lined out in <https://docs.snowflake.com/en/sql-reference/functions/to_timestamp#usage-notes>. Currently Snowpark does support integers in the range of an 8-byte signed integer only.

Example::
>>> df = session.createDataFrame([20, 31536000000], schema=['a'])
>>> df.select(to_timestamp(col("a"))).collect()
[Row(TO_TIMESTAMP("A")=datetime.datetime(1970, 1, 1, 0, 0, 20)), Row(TO_TIMESTAMP("A")=datetime.datetime(2969, 5, 3, 0, 0))]
>>> df.select(to_timestamp(col("a"), lit(9))).collect()
[Row(TO_TIMESTAMP("A", 9)=datetime.datetime(1970, 1, 1, 0, 0)), Row(TO_TIMESTAMP("A", 9)=datetime.datetime(1970, 1, 1, 0, 0, 31, 536000))]
Copy

Larger numbers stored in a string can be also converted via this approach

Example::
>>> df = session.createDataFrame(['20', '31536000000', '31536000000000', '31536000000000000'], schema=['a'])
>>> df.select(to_timestamp(col("a")).as_("ans")).collect()
[Row(ANS=datetime.datetime(1970, 1, 1, 0, 0, 20)), Row(ANS=datetime.datetime(1971, 1, 1, 0, 0)), Row(ANS=datetime.datetime(1971, 1, 1, 0, 0)), Row(ANS=datetime.datetime(1971, 1, 1, 0, 0))]
Copy
Language: English