Why does the DATE() function return the next date after 6:00 PM?
I have a dataset with a Date and Time stamp field. When I convert it to a date field using DATE(`date_field`) I am finding some unexpected results.
The only way that I was able to get the intended results was to subtract 6 hours from the timestamp first:
Is this an intended function of the DATE() function, or am I doing something wrong?
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
Best Answers
-
Have you tried using the Convert_TZ() fundtion as a second argument ?
here it is an simple example of how to use it
DATEDIFF(Date(CONVERT_TZ(now(),'UTC','EST')),`TransactionDate`)
this give us the number of days from current date/time
Domo Arigato!
**Say 'Thanks' by clicking the thumbs up in the post that helped you.
**Please mark the post that solves your problem as 'Accepted Solution'0 -
The CONVERT_TZ function automatically accounts for daylight savings when using long time zone names
for example:
CONVERT_TZ(`Date`, '+00:00', '+10:00')
CONVERT_TZ(`Date`,'UTC','American/Chicago')Domo Arigato!
**Say 'Thanks' by clicking the thumbs up in the post that helped you.
**Please mark the post that solves your problem as 'Accepted Solution'1 -
Try this one out.
convert_tz(`Date and time of Last Change`,'utc','us/central')
-----------------
Chris2
Answers
-
It does the same thing on my flows so we have to adjust for UTC time since we are in EST time, always substracting 4 or 5 hours depending of daily savings time to prevent that issue
Domo Arigato!
**Say 'Thanks' by clicking the thumbs up in the post that helped you.
**Please mark the post that solves your problem as 'Accepted Solution'0 -
How do you determine the number of hours to subtract though? I would like to prevent the need to change my dataflow every 6 months or so
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman0 -
Have you tried using the Convert_TZ() fundtion as a second argument ?
here it is an simple example of how to use it
DATEDIFF(Date(CONVERT_TZ(now(),'UTC','EST')),`TransactionDate`)
this give us the number of days from current date/time
Domo Arigato!
**Say 'Thanks' by clicking the thumbs up in the post that helped you.
**Please mark the post that solves your problem as 'Accepted Solution'0 -
I feel like this runs into the same issues. At some point you would need to change from
CONVERT_TZ(now(),'UTC','EST')
to
CONVERT_TZ(now(),'UTC','EDT')
I can't find a way to automate the switch, given that the actual date changes each year
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman0 -
The CONVERT_TZ function automatically accounts for daylight savings when using long time zone names
for example:
CONVERT_TZ(`Date`, '+00:00', '+10:00')
CONVERT_TZ(`Date`,'UTC','American/Chicago')Domo Arigato!
**Say 'Thanks' by clicking the thumbs up in the post that helped you.
**Please mark the post that solves your problem as 'Accepted Solution'1 -
I thought we had a solution here, but I'm not able to get the long timezone name to work:
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman0 -
Try this one out.
convert_tz(`Date and time of Last Change`,'utc','us/central')
-----------------
Chris2 -
-
@ST_-Superman-_ its got to take more than one to be able to help the man of steel ? (Superman theme song playing in the backgorund)
@DaniBoy should take @cwolman and me up a Belt in the Dojo for doing this LOL !
Have a good day guys
Domo Arigato!
**Say 'Thanks' by clicking the thumbs up in the post that helped you.
**Please mark the post that solves your problem as 'Accepted Solution'2 -
This is an AWESOME example of community collaboration!
Well done @Godiepi @cwolman and @ST_-Superman-_
Enjoy your new belts! You guys are all Super!
Cheers!
2
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 292 Workbench
- 4 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 95 SQL DataFlows
- 602 Datasets
- 2.1K Magic ETL
- 3.7K Visualize
- 2.4K Charting
- 694 Beast Mode
- 43 App Studio
- 39 Variables
- 658 Automate
- 170 Apps
- 441 APIs & Domo Developer
- 42 Workflows
- 5 DomoAI
- 32 Predict
- 12 Jupyter Workspaces
- 20 R & Python Tiles
- 386 Distribute
- 111 Domo Everywhere
- 269 Scheduled Reports
- 6 Software Integrations
- 113 Manage
- 110 Governance & Security
- 8 Domo University
- 30 Product Releases
- Community Forums
- 39 Getting Started
- 29 Community Member Introductions
- 98 Community Announcements
- Domo Community Gallery
- 4.8K Archive