Understand datePart formula
Hi - I am seeing something odd in my prep recipe and wondering what I am missing.
I have a date and would like to take just the day piece of it. So for 2020-06-13 I would expect the day to be 13. When I use datepart(mydate,'days') I get 12.
What is even odder is that if my date (i.e. 2019-01-01T00:00:00) is the first of the month it returns 31.
For instance datePart(asDate('2020-01-01','yyyy-MM-dd'),'days') = 31 and not 1.
I tried datePart(now(),'days') and it correctly gave me 27 for today.
Is there anyway to get the actual day for a date or does anyone know why this is giving me odd results? Is there a problem with there not being a value for time?
Thanks for all the help in advance!
Best Answer
-
tgb417 Dataiku DSS Core Designer, Dataiku DSS & SQL, Dataiku DSS ML Practitioner, Dataiku DSS Core Concepts, Neuron 2020, Neuron, Registered, Dataiku Frontrunner Awards 2021 Finalist, Neuron 2021, Neuron 2022, Frontrunner 2022 Finalist, Frontrunner 2022 Winner, Dataiku Frontrunner Awards 2021 Participant, Frontrunner 2022 Participant, Neuron 2023 Posts: 1,598 Neuron
My guess is that we are having a time zone problem.
now()
produces a date like 2020-08-27T14:33:48.244Z This is in Zulu or GMT time zone. Note that the local time for me is actually 10:33am EDT. Eastern Daylight Saving Time is actually GMT -4 hours.
On my system
asDate('2020-01-01','yyyy-MM-dd')
produces 2020-01-01T00:00:00.000Z
Note that this is in GMT +/- 0
In looking at the documentation I note for the function datePart the
Returned parts are always in local timezone and english.
For me
datePart(asDate('2020-01-01','yyyy-MM-dd'),'days')
also produces 31
datePart(asDate('2020-01-01','yyyy-MM-dd'),'month')
produces 12
for me because this GMT time is actually on December 31st my Local Time. (Eastern Standard Time)
On January 1 of any year I am GMT -5. I can get the correct answer by entering.
datePart(inc(asDate('2020-01-01','yyyy-MM-dd'), 5,'hours'),'month')
Which adds 5 hours to the date '2020-01-01' producing
2020-01-01T05:00:00.000Z
This time is actually the First of January 2020 in my time zone.
However, this does not seem like a great solution, because it does not work when we go from EDT to EST and back again. My offset from GMT is either -4 or -5 depending on the time of year.
I was able to get better results by doing the following.
datePart(asDate('2020-07-01 EST','yyyy-MM-dd z'),'days')
Where "EST" is my base time zone here in the eastern USA.
and "z" is the parser for time zone in asDate function
I know that this is not intuitive and I hope this is of some help.
Hay Dataiku staff is there a better way to do this now? In the future is there anything that can be done to make Dates and Time more intuitive in DSS.
Answers
-
Hi - thank you for the above - I thought maybe it was something to do with the time zone. I decided to not use the formula and go with the following:
toNumber(substring(toString(entry_date),8,10))
This seems to work no matter what the time is and will give the day of the date.
-
tgb417 Dataiku DSS Core Designer, Dataiku DSS & SQL, Dataiku DSS ML Practitioner, Dataiku DSS Core Concepts, Neuron 2020, Neuron, Registered, Dataiku Frontrunner Awards 2021 Finalist, Neuron 2021, Neuron 2022, Frontrunner 2022 Finalist, Frontrunner 2022 Winner, Dataiku Frontrunner Awards 2021 Participant, Frontrunner 2022 Participant, Neuron 2023 Posts: 1,598 Neuron
Yes, that is true as long as your strings are of a stable format.
There is always more than one way to solve a problem.
-
Hi Dataiku staff - would you be able to reply to Neuron's question to you? The reason I ask is that we found if we store our data set in hive and then have users access it from Excel, the timezone is causing issues because the dates are showing the prior day because we are on US Eastern timezone. I can adjust to make sure the time is late in the day UTC but this definitely is not the greatest solution.
Thanks for all the help!
-
CoreyS Dataiker Alumni, Dataiku DSS Core Designer, Dataiku DSS Core Concepts, Registered Posts: 1,150 ✭✭✭✭✭✭✭✭✭
Hi @aw30
while you wait for a more complete response, let me see if I can help. Here are two resources I wanted to share just incase you haven't already been able to access them.The first is from the Knowledge Base and it is: How Dataiku DSS Handles and Displays Date & Time
The other is from our Documentation and it is: Managing dates
Hopefully these help and if they don't let me know and I will try to provide additional support.