Timestamp as Float (double) causing error, can't be cast

I have a PostgreSQL column with timestamp data stored in a float, seconds since epoc with milliseconds as after the decimal point. For example 1678477483.628172 is Friday, March 10, 2023 7:44:43.628 PM.

This isn't my data, so I have no control over the format.

  • In its default state, this column functions normally.
  • I am not given the option of casting it to a different type under the "gear" menu
  • Setting it to a "Creation Timestamp" semantic type causes the table's summary page to error with:
    Output of update-field-options does not match schema: e[0;33m (named [nil nil (named {:binning {:strategy (not (#{:default :bin-width :num-bins} :day-of-week))}} "options")] "Must be a valid instance of one of these clauses: :expression, :field") e[0m

How can I proceed with using this field as the timestamp that it is?

You need to build a model to transform this field into something parseable by a query

That seems like a decent workaround, but to be clear, I shouldn't have to do that, right? I'm new to Metabase, but the workflow that seems to be laid out is:

  • Annotate, cast, and add types to tables in the Data Model Admin
  • Then build higher-level abstractions on them with Models

Is that correct?

For this specific case it’s the only way as the timestamp seems to be in a format we don’t seem to support natively, or that’s what I think