Convert to timestamp

vaco

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')

rco

@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)

rco

What about this?:

REPLACE(process_timestamp, 'T', ' ')

vaco

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.

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

akeating

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.

vaco

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

@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)

vaco

It is working! thanks @rco 😀