Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

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.

Need Help
  • v-cherch-msft's avatar
    v-cherch-msft
    8 years ago

    Hi Anonymous

     

    You may try to create a column as below.

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

     

    Regards,

    Cherie

4 Replies

  • v-cherch-msft's avatar
    v-cherch-msft
    Microsoft Employee
    8 years ago

    Hi Anonymous

     

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

     

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

    Regards,

    Cherie

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      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.

      • v-cherch-msft's avatar
        v-cherch-msft
        Microsoft Employee
        8 years ago

        Hi Anonymous

         

        You may try to create a column as below.

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

         

        Regards,

        Cherie