Convert to timestamp

vaco
vaco Member
in Magic ETL

Hi, I have a process_timestamp column with string values like this: 2024-09-17T14:47:41. I am trying to convert it from a string to a timestamp in the format 2024-09-17 14:47:41. I used the formulas below in my ETL, but they are not working, and I get this error message "failed to convert value from type string to type timestamp". Could you please help me with the conversion? Thanks

DATE(`process_timestamp`)

DATE_FORMAT(STR_TO_DATE(`process_timestamp`, '%Y-%m-%dT%H:%i:%s'), '%Y-%m-%d %H:%i:%s')

DATE_FORMAT(`process_timestamp`,'%Y-%m-%d %h:%i:%s')

Best Answer

  • rco
    rco Domo Employee
    Answer ✓

    @vaco Timestamps and Dates columns in Magic ETL don't have a format. They will always be displayed in a way that is determined by the table or card you're looking at them in, along with your company settings like timezone and locale.

    If you want to force a specific format everywhere, a string column is what you need.

    As for the conversion failures, from your latest message it sounds like you have strings in this column that aren't Timestamps at all, like '—'. You'll either need to handle those strings specifically, like this:

    case when process_timestamp in ('—', 'N/A', 'other value…') then null else cast(process_timestamp as timestamp) end

    Or, if you can be confident that you'll never need to look at any value that isn't already a valid Timestamp in the column, you can simply use:

    try_cast(process_timestamp as timestamp)

    Randall Oveson <randall.oveson@domo.com>

Answers

  • rco
    rco Domo Employee

    What about this?:

    REPLACE(process_timestamp, 'T', ' ')

    Randall Oveson <randall.oveson@domo.com>

  • vaco
    vaco Member

    Thanks @rco , this formula is removing the T, but I still have a string. I tried to use the Alter columns tile to convert from string to timestamp, but it is not working.

    image.png

    What formula will you use now to convert from string to timestamp?

  • akeating
    akeating Contributor
    edited November 20

    Hi @vaco,

    I use a formula like this in a beast mode for the same purpose: CONCAT(Date('process_timestamp'), ' ', DATE_FORMAT('process_timestamp','%T'))

    Edit: Sorry, I just realized you're not looking for a string as your result.

    **Was this post helpful? Click Agree, Like, or Awesome below.**
    **Did this solve your problem? Accept it as a solution!**

  • vaco
    vaco Member

    Thanks @akeating , I used your formula in my ETL, but I am still getting the same error message Failed to convert value '—' from type string to type date. Please do you know another formula?

  • rco
    rco Domo Employee
    Answer ✓

    @vaco Timestamps and Dates columns in Magic ETL don't have a format. They will always be displayed in a way that is determined by the table or card you're looking at them in, along with your company settings like timezone and locale.

    If you want to force a specific format everywhere, a string column is what you need.

    As for the conversion failures, from your latest message it sounds like you have strings in this column that aren't Timestamps at all, like '—'. You'll either need to handle those strings specifically, like this:

    case when process_timestamp in ('—', 'N/A', 'other value…') then null else cast(process_timestamp as timestamp) end

    Or, if you can be confident that you'll never need to look at any value that isn't already a valid Timestamp in the column, you can simply use:

    try_cast(process_timestamp as timestamp)

    Randall Oveson <randall.oveson@domo.com>

  • vaco
    vaco Member

    It is working! thanks @rco 😀

Categories