Convert to timestamp
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
-
@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>
1
Answers
-
What about this?:
REPLACE(process_timestamp, 'T', ' ')
Randall Oveson <randall.oveson@domo.com>
0 -
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!**0 -
@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>
1
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 744 Beast Mode
- 58 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 395 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 6 Software Integrations
- 125 Manage
- 122 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive