Understand datePart formula

Solved!
aw30
Level 4
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!

0 Kudos
1 Solution
tgb417

@aw30 

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.

 

--Tom

View solution in original post

0 Kudos
5 Replies
tgb417

@aw30 

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.

 

--Tom
0 Kudos
aw30
Level 4
Author

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.

0 Kudos
tgb417

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.

--Tom
0 Kudos
aw30
Level 4
Author

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

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.

Looking for more resources to help you use Dataiku effectively and upskill your knowledge? Check out these great resources: Dataiku Academy | Documentation | Knowledge Base

A reply answered your question? Mark as โ€˜Accepted Solutionโ€™ to help others like you!
0 Kudos