How to convert hubspot timestamps to meaningful datetime?

SOLVE
oren_hac
Participant

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

1 Accepted solution

Accepted Solutions
oren_hac
Solution
Participant

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

View solution in original post

2 Replies 2
oren_hac
Solution
Participant

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

View solution in original post

rentiner
Participant

I was breaking my head until I found your own answer

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