Skip to main content
Solved

Separate date and time from timestamp

  • October 1, 2020
  • 3 replies
  • 85 views
A

I have four fields:

  1. Unix Timestamp - a single line text field that contains the unix timestamp

  2. Date & Time - a formula field that converts the unix timestamp into a readable date and time using the following formula: DATEADD(‘1/1/1970’,{Unix Timestamp},‘seconds’)

  3. Date - where I wish to extract just the date from the Date & Time field

  4. Time - where I wish to extract just the time from the Date & Time field

How do I get numbers 3 and 4 to work?

Best answer by kuovonne

You can use DATETIME_FORMAT for both 3 and 4. It is documented in the formula field reference.

For the time, you will probably also want to use SET_TiMEZONE to set the timezone or you will get the time as GMT time.

date

DATETIME_FORMAT(
  SET_TIMEZONE({Date & Time field}, 'Australia/Sydney'), 
  'M/D/YYYY'
)

time

DATETIME_FORMAT(
  SET_TIMEZONE({Date & Time field}, 'Australia/Sydney'), 
  'h:mm'
)

Note that you can also combine these formulas with formula use in step 2, to eliminate the field in step 2.

DATETIME_FORMAT(
  SET_TIMEZONE(
    DATEADD('1/1/1970',{Unix Timestamp},'seconds'), 
    'Australia/Sydney'
  ), 
  'M/D/YYYY'
)

    3 replies

    kuovonne
    Forum|alt.badge.img+29
    • kuovonne
    • Brainy
    • Answer
    • October 1, 2020

    You can use DATETIME_FORMAT for both 3 and 4. It is documented in the formula field reference.

    For the time, you will probably also want to use SET_TiMEZONE to set the timezone or you will get the time as GMT time.

    date

    DATETIME_FORMAT(
  SET_TIMEZONE({Date & Time field}, 'Australia/Sydney'), 
  'M/D/YYYY'
)

    time

    DATETIME_FORMAT(
  SET_TIMEZONE({Date & Time field}, 'Australia/Sydney'), 
  'h:mm'
)

    Note that you can also combine these formulas with formula use in step 2, to eliminate the field in step 2.

    DATETIME_FORMAT(
  SET_TIMEZONE(
    DATEADD('1/1/1970',{Unix Timestamp},'seconds'), 
    'Australia/Sydney'
  ), 
  'M/D/YYYY'
)
    T
    A
    rhafer
    A
    • Alba_Machado
    • Author
    • Known Participant
    • October 1, 2020

    You can use DATETIME_FORMAT for both 3 and 4. It is documented in the formula field reference.

    For the time, you will probably also want to use SET_TiMEZONE to set the timezone or you will get the time as GMT time.

    date

    DATETIME_FORMAT(
  SET_TIMEZONE({Date & Time field}, 'Australia/Sydney'), 
  'M/D/YYYY'
)

    time

    DATETIME_FORMAT(
  SET_TIMEZONE({Date & Time field}, 'Australia/Sydney'), 
  'h:mm'
)

    Note that you can also combine these formulas with formula use in step 2, to eliminate the field in step 2.

    DATETIME_FORMAT(
  SET_TIMEZONE(
    DATEADD('1/1/1970',{Unix Timestamp},'seconds'), 
    'Australia/Sydney'
  ), 
  'M/D/YYYY'
)

    Kuovonne to the rescue again! Thanks! The date and time formulas worked perfectly. The last formula didn’t work for me, but I don’t mind having an extra column that I could hide. :slightly_smiling_face:

    kuovonne
    Forum|alt.badge.img+29
    • kuovonne
    • Brainy
    • October 1, 2020

    Kuovonne to the rescue again! Thanks! The date and time formulas worked perfectly. The last formula didn’t work for me, but I don’t mind having an extra column that I could hide. :slightly_smiling_face:


    Some curly quotes snuck into the last formula when I copy/pasted from your earlier post. I edited my post to turn them in straight quotes, so you can try it again.

    Terms & ConditionsAccessibility statement