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....
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

v-cherch-msft's avatar
v-cherch-msft
Icon for Microsoft Employee rankMicrosoft 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
      Icon for Microsoft Employee rankMicrosoft 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

      • charleshale's avatar
        charleshale
        Icon for Continued Contributor rankContinued Contributor
        6 years ago

        hmm I get an error when I use this in M query:

         

        Expression.Error: We cannot apply operator - to types Number and DateTime.
        Details:
            Operator=-
            Left=1583959530
            Right=1/1/1970 12:00:00