Time Format
Best Answers
-
How are you combining your date and time fields? What format is your time field in?
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1 -
Try this:
DATE_ADD(`Timecard_Pay_Date`, interval time_to_sec(`Time_Out`) second)
Its converting your time string to the number of seconds and then adding that to your date since dates have an inherent time of midnight
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1 -
Thanks Grant!
0
Answers
-
How are you combining your date and time fields? What format is your time field in?
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1 -
Thanks Grant for your prompt answer. My date is formatted as date. After I combined the date and time, i changed it to timestamp.
0 -
What is the format of your Time_Out field?
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1 -
As a time stamp i got the example above.
I changed it to text and now i got this:
I need to get it in this format:
Thanks again Grant.
0 -
@Liliana If you have access to ETL 2.0 then you can add a formula tile with Date and combine with a couple of functions as shown below:
Raw data: Thu Sep 02 00:00:00 GMT 2021 07:49 AM
Output: 2021-09-02 07:49:00 (format in timestamp)
Formula: timestamp(CONCAT(split_part(`Date`,' ',3), ' ', split_part(`Date`,' ',2), ' ',split_part(`Date`,' ',6),' ',split_part(`Date`,' ',7),' ',split_part(`Date`,' ',8),' ',split_part(`Date`,' ',5)))
The idea here is to rearrange texts in your string in a format that can be recognized by domo and converted to timestamp. You can also use Split Columns tile and combine tiles to do the same.
Best, TJ
0 -
Hi @Liliana ,
Rather than trying to combine the date and time as text, I would suggest using this approach:
- Create a duplicate of the Time_Out column using a Combine Columns tile, using "None" as your character separating and selecting only Time_Out in the column list (Do not check the box to remove the columns being combined)
- Your new column will default to text, so use a Set Column Type tile to change it to Date (Month First)
- Add a Date Operations tile to calculate the number of minutes after midnight your timestamp is by using the "Difference between dates" operation, Minutes as your unit of measurement, and calculating the original Time_Out column minus the date only copy you just made
- Use another Date Operations tile to add that number of minutes to the Timecard_Pay_Day column by selecting the "Add to date" operation with Minutes as your unit of measurement. Add Timecard_Pay_Day plus the minutes column you calculated in the previous step
Hope this helps!
1 -
Thank you @tejusarora and @MichelleH for your answers. I will try your suggestions.
1 -
Try this:
DATE_ADD(`Timecard_Pay_Date`, interval time_to_sec(`Time_Out`) second)
Its converting your time string to the number of seconds and then adding that to your date since dates have an inherent time of midnight
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1 -
Thanks Grant!
0 -
Glad I could help @Liliana. If you could accept my answer so others can find it easier I'd appreciate it! Thanks.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 297 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 614 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 53 App Studio
- 40 Variables
- 677 Automate
- 173 Apps
- 451 APIs & Domo Developer
- 45 Workflows
- 8 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- Domo Community Gallery
- 32 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive