pythondatetimepython-polars

Cast pl.Date to Unix epoch


Trying to convert a pl.Date column to UNIX epoch as is, without any timezone offset:

import datetime
import polars as pl

df = pl.DataFrame(
    {'Date': [datetime.datetime.now().date()]}
)

Correct time (00:00:00) when converted to Datetime:

df.with_columns(
    pl.col("Date").cast(pl.Datetime)
)
┌─────────────────────┐
│ Date                │
│ ---                 │
│ datetime[μs]        │
╞═════════════════════╡
│ 2023-06-10 00:00:00 │
└─────────────────────┘

Incorrect time when casting to timestamp:

datetime.datetime.fromtimestamp(
    df.with_columns(
        pl.col("Date").cast(pl.Datetime).dt.timestamp("ms").truediv(1_000)
    ).item()
)
datetime.datetime(2023, 6, 10, 8, 0) # (08:00:00)

As suggested, without casting to Datetime also produces the incorrect time. (08:00:00)

pl.col("Date").dt.timestamp("ms").truediv(1_000)

Solution

  • Note that vanilla Python datetime defaults to local time if you don't set a time zone (naive datetime). In contrast, polars assumes naive datetime to resemble UTC (as pandas does as well).

    Keep it consistent by setting the time zone, e.g. UTC:

    from datetime import datetime, timezone
    import polars as pl
    
    df = pl.DataFrame(
        {'Date': [datetime.now(timezone.utc).date()]}
    )
    
    df = df.with_columns(
        pl.col("Date").cast(pl.Datetime).dt.timestamp("ms").truediv(1_000).alias("Unix")
    )
    
    print(df)
    # shape: (1, 2)
    # ┌────────────┬──────────┐
    # │ Date       ┆ Unix     │
    # │ ---        ┆ ---      │
    # │ date       ┆ f64      │
    # ╞════════════╪══════════╡
    # │ 2023-06-10 ┆ 1.6864e9 │
    # └────────────┴──────────┘
    
    print(datetime.fromtimestamp(df["Unix"][0], timezone.utc))
    # 2023-06-10 00:00:00+00:00