How to convert Unix seconds to date type in staging macro

AutomateDV (formerly known as dbtvault)
1

Hi ,
I want to convert Unix seconds like (12345678)format to date YYYY-MM-DD in staging macro .
I am using bigquery with automatedv

2

Hi!

You should be able to use the built-in BigQuery timestamp functions as a derived column in your primed staging layer (any model that uses the stage() macro):

derived_columns:
    NEW_COLUMN: 'FORMAT_TIMESTAMP("%Y-%m-%d", TIMESTAMP_SECONDS(OLD_COLUMN), "UTC")'

where NEW_COLUMN is the alias for the new Datetime column and OLD_COLUMN is the column containing your current UNIX timestamp. Note the UTC specified here - modify this as appropriate for your data.

See the BigQuery docs here:

AutomateDV Docs on functions in derived columns:

Hope this helps!

3 Likes