Datetime in DataFrames

While being at it.

Not clear about the Date(time) in Explorer anyway. I wrote it as utc_datetime in the SQLite with Ecto. Than in the DB the date is a text (why). Explorer does read it as text and does not infer it from the format (which is okay, because the db should have the correct type in the first place.

The strings has the format of 2023-08-29T15:38:00 I convert the Series of strings to a list of DateTime with

dt = S.to_list(df["date"])
|> Enum.map(&elem(DateTime.from_iso8601(&1 <>"+00:00"),1))

a) Why couldn’t I not write with DateTime.from_iso8601(&1, :basic, "ETC/UTC")
b) Why cann’t I convert the list to a Series? Error: unsupported datatype: ~U[2023-08-29 15:38:00Z]
c) Why cannot I write it into the DataFrame with put? Sane error as in b)

Minimum example for a)

test_dates = S.from_list(["2023-08-29T15:38:00", "2023-08-29T14:17:28", "2023-08-29T12:53:17",])
dt0 = Enum.at(S.to_list(test_dates), 0)
DateTime.from_iso8601(dt0, :basic, "ETC/UTC")

Throws the error:

FunctionClauseError) no function clause matching in Calendar.ISO.parse_utc_datetime/2    
    
    The following arguments were given to Calendar.ISO.parse_utc_datetime/2:    
        # 1
        "2023-08-29T15:38:00"
    
        # 2
        "ETC/UTC"
    
    Attempted function clauses (showing 1 out of 1):
    
        def parse_utc_datetime(string, format) when is_binary(string) and format === :basic or format === :extended

Which is strange because I added the format as :basic …

Edit: In the end I only want to be able to filter the DataFrame by a minimum date and/or a maximum date.

SQLite doesn’t support a specific “date” type - only null / integer / real / text / blob

That’s not the arguments that from_iso8601 is documented to accept; why would you expect that to work? The second argument should be a calendar module and the third should be either :basic or :extended.

To your original question, the Series.strptime function seems like exactly what you’re looking for - takes a series of string values and parses them into a datetime series.

1 Like

Explorer only accepts NaiveDateTime. The following should work:

datetime_series =
  ["2023-08-29T15:38:00", "2023-08-29T14:17:28", "2023-08-29T12:53:17"]
  |> Enum.map(&NaiveDateTime.from_iso8601!/1)
  |> S.from_list()

# #Explorer.Series<
#   Polars[3]
#   datetime[μs] [2023-08-29 15:38:00.000000, 2023-08-29 14:17:28.000000,
#    2023-08-29 12:53:17.000000]
# >

Then you should be able to compute the min/max no problem:

S.max(datetime_series)
# ~N[2023-08-29 15:38:00.000000]

S.min(datetime_series)
# ~N[2023-08-29 12:53:17.000000]
2 Likes