How to convert hubspot timestamps to meaningful datetime?

[oren_hac]

Hi,

We're importing the hubspot DB to our BigQuery.

On the hs.properties.hs_lifecyclestage_marketingqualifiedlead_date.value property I see strange value such as: 1525801948522, 1525774682022.

I could not figure out what timestamp they represent? (These are not Unix timestamps)
I want to convert them to a datetime which I could actually use SQL queries on.

Does anyone have an idea?

Many thanks,
Oren

[Stanz]

I know it's been three years, but I'm running into the same problem and I'm new to SQL. Can you show me how you've done this in BigQuery?

[fsilva]

Just use the Bigquery  TIMESTAMP_MILLIS  function 

In my case, the date from engagement table is a string so:

TIMESTAMP_MILLIS(CAST(created_at AS INT64))

[oren_hac · Accepted solution]

Replying to myself.
It is unix time - just need to divide by 1000.

[rentiner]

I was breaking my head until I found your own answer

$date = date("Y-m-d H:i:s", ($date/1000) );