Community Conundrum 25: Feature Visualization is now live!

# 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!

3 Replies
Level 6

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
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.

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