topic Re: Datetime conversion on streaming tables in Warehousing & Analytics

Datetime conversion on streaming tables

singh_tushar_14 — Thu, 11 Sep 2025 13:01:15 GMT

I am using streaming tables to read from multiple parquet files and create a table in my raw layer.

Tech Stack -- dbt-databricks

While loading I have a column called "ets" in my source which is nothing "milliseconds since Jan 1st 1970."

I want this to be converted into a timestamp format for which I use

timestampadd(millisecond, ets, '1970-01-01') ets = 1746046131518 expected_output = 2025-04-30T20:48:51.518+00:00 actual_output = 1969-12-08T18:54:02.046+00:00

Running it as a dry code on databricks notebook gives the correct output but when compiled through dbt and ran as a model same code gives a different output.

DBT Model code

SELECT *, {{ dbt.dateadd("MILLISECOND", "ets", "'1970-01-01'") }} AS ets_converted, {{ add_metadata_fields() }} FROM STREAM read_files( 'path/to/volume', format => 'parquet', header => TRUE )

Compiled DBT Code from Databricks.

CREATE STREAMING TABLE `catalog_name`.`schema_name`.`table_name` (<inferredSchema>) SELECT *, timestampadd(MILLISECOND, ets, '1970-01-01') AS ets_converted, CURRENT_TIMESTAMP() AS meta_loaded_at FROM STREAM read_files( 'path/to/volumes', format => 'parquet', header => TRUE )

I am not able to make an assumption is this thing wrong with dbt or streaming table or code.

Re: Datetime conversion on streaming tables

szymon_dybczak — Thu, 11 Sep 2025 13:26:00 GMT

Hi @singh_tushar_14 ,

Since your ets columns already contains integer that represents milliseconds since Jan 1st 1970, can't you just use Spark SQL fuction? You don't need to add anything to 1970-01-01, since that information is already "encoded" in your ets attribute:

%sql SELECT timestamp_millis(1746046131518)

Re: Datetime conversion on streaming tables

singh_tushar_14 — Thu, 11 Sep 2025 13:37:23 GMT

Agreed. There is nothing wrong with the above code as well. Logically it should result in the same value.
I am seeing problem when running this as a sql model through dbt.

I tried 10 runs consecutively and it gave me the wrong outputs again and again (not the databricks cell run). On the 11th run the same code results in a different output. Is this kind of a bug or something ?

Re: Datetime conversion on streaming tables

szymon_dybczak — Thu, 11 Sep 2025 13:48:05 GMT

Maybe there's something wrong with datatypes? Could you provide data type of that column?

Re: Datetime conversion on streaming tables

singh_tushar_14 — Fri, 12 Sep 2025 05:12:46 GMT

Datatype - BIGINT. Sample Data

ets = 1754013182959