Community Conundrum 25: Feature Visualization is now live! Read More

Understand datePart formula

Level 3
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
3 Replies
Level 6

@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
Level 3
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
Level 6

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