cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
mford Frequent Visitor
Frequent Visitor

Converting DateTime into Unix Timestamp integer (calculated column & measure)

I see lots of posts on converting a unix timestamp integer into a datetime (timestamp).  How would one take a DateTime value (2018-08-22 10:11:12) and convert it into a a unix timestamp integer (i.e. number of seconds after Jan 1 1970).

 

I would like to do it in a calculated column (M), but it would be nice to know how to do it in a measure as well (DAX). Thanks.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Converting DateTime into Unix Timestamp integer (calculated column & measure)

Hi @mford

 

You may try to create a column as below.

Column=Duration.TotalSeconds([Column1]-#datetime(1970, 1, 1, 0, 0, 0))

1.png

 

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3
Community Support Team
Community Support Team

Re: Converting DateTime into Unix Timestamp integer (calculated column & measure)

Hi @mford

 

You may try this measure to convert Date Time into Unix Timestamp integer.

 

Measure =
DATEDIFF ( DATE ( 1970, 1, 1 ), MAX ( Table2[Column1] ), SECOND )

1.png

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
mford Frequent Visitor
Frequent Visitor

Re: Converting DateTime into Unix Timestamp integer (calculated column & measure)

Thank you.

I did some trial and error with a Calculated Column (M code) and think I got this to work correctly.

 

= Table.AddColumn(#"Changed Type", "TimestampUNIX", each Duration.TotalSeconds([Timestamp] - DateTime.FromText("1970-01-01 00:00:00")))

Someone can tell me if they see a problem with this or have a more efficient method.

Community Support Team
Community Support Team

Re: Converting DateTime into Unix Timestamp integer (calculated column & measure)

Hi @mford

 

You may try to create a column as below.

Column=Duration.TotalSeconds([Column1]-#datetime(1970, 1, 1, 0, 0, 0))

1.png

 

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.