Reformatting Date Columns: yyyy D kkmm

[thomas whiteParticipant]

I’m working with a very awkward data source. The provide data in the format:

Year: yyyy

DOY: D

Time: (h)hmm

The problem the data source doesn’t put a leading zero on the hour. If the description doesn’t make sense I’ve attached a screenshot. Can someone suggest the best way to reformat the Hour field so I can use a DG Date Column.

 

[jibParticipant]

Something like this?  I use an intermediate column converting the individual date/time related columns to a single number; seconds since 1970. Then an expression column to get it to a date_time value

[dgteamModerator]

In the first approach, every column was set to a date column and using ICU strings to map to a meaningful date, but the day of year column actually has the year of 2000, which is another problem, and the reason that the year is so far off in 2051.  So even if the hours worked this would still be a problem.

In the second approach, every column is a number column and used to calculate the seconds since 1970 (the internal date format). This is on the right track but you have to very careful when estimating seconds across multiple years because of those pesky leap years!  You can tell something is not quite right here if you check the day of the year – it must be in December.

What seems to work as a combination of both approaches. For the year you can use the date column and then you’ll get the appropriate seconds for the beginning of that particular year. For the other two columns you can have those as number columns. These can be combined in an expression.

Notice how you can also display expressions as dates directly.  Here we pick a display format that shows the date and time.

Also, with some trial and error we realized that the time fraction needs to be subtracted. Otherwise you’re calculating the seconds at the end of the day and then moving to the next day when you add time.

To check this, there are two useful functions dayofyear and time.

Here is the result, where the 2400 row is actually the zero hour of the next day.

Note that if you have year, month, day then we have a built-in function for this called seconds(year,month,day).  This function is very useful for that type of format however that’s not quite what you have here since you have the day of year and time.

Hope this helps!

 

[Author]

Posts