convert "number of days since" to datetime, given 365-day and 360-day calendars (not Gregorian)

95 views (last 30 days)
Ellen Maas on 23 Jul 2019
Answered: Eric Sofen on 29 Jul 2019
Hi, I have some regional climate model data that is dated by "days since 1949-12-1". None of the models use a Gregorian calendar (365/366 days/year depending on leap year). They either use a "365-day" calendar (no Feb 29 in any year) or "360-day" calendar (every month is exactly 30 days).
I found two functions in the Financial Toolbox that can generate the number of days between two dates, based on either a 365- or 360-day calendar (days365 and days360, respectively).
But I need to do the reverse. I have the number of days since a point in time, and I need the dates. I'm currently using "caldays", but it doesn't appear to have the calendar type as an option. This is the code I'm currently using, before I discovered the usage of different calendars:
var_time = importdata('var_time.txt');
base_date = datetime(1949,12,1);
% round 'time' attribute to nearest integer, add the number of days it
% represents to the baseline date, then extract the month and year from
% the new date generated
data_mx = day(base_date+caldays(floor(var_time))); % day of month
data_mx(:,2) = month(base_date+caldays(floor(var_time))); % month number
data_mx(:,3) = year(base_date+caldays(floor(var_time))); % year
% results in 365 days ending with 12/30/1952, a leap year
% should exclude 2/29/1952 and end with 12/31/1952
Anyone know of any Matlab functions I'm missing, or coding ideas to accomplish this?
Thanks.
UPDATE: I've added a new file: var_time_mx.txt which includes data from the beginning of the dataset (1/1/1950) through 1956, so it includes two leap years (1952 and 1956). Details about it are in the comment thread with @Walter Robertson, below.

Walter Roberson on 23 Jul 2019
calyears(days/days_per_year) + base_datetime
Ellen Maas on 24 Jul 2019
Thank you for the suggestion. It looks like this would reset where 12/1 would fall after every 365 days. I've attached a matrix with expanded data from the beginning of the data set, where the first record represents 1/1/1950 (because the first record in var_time = 31.5, the number of days since 12/1/1949) and included all the records through what I'm currently calculating as 12/31/1956, so it includes two leap years.
The first column is the "days since 12/1/1949", such as the var_date.txt file I originally included. The remaining columns were calculated as:
var_time(:,2) = var_time(:,1)/365; % fraction of 365-day year that the "number of days since" represents
var_time(:,3) = floor(var_time(:,2)); % strip off the decimal places from above
var_time(:,4) = day(base_date+caldays(floor(var_time(:,1))),'dayofyear'); % day of the year
var_time(:,5) = day(base_date+caldays(floor(var_time(:,1)))); % day of month
var_time(:,6) = month(base_date+caldays(floor(var_time(:,1)))); % month number
var_time(:,7) = year(base_date+caldays(floor(var_time(:,1)))); % year
var_time(:,8) = year(calyears(var_time(:,3)) + base_date); % every date calculated as 12/1/yyyy, so just extract the year for illustration
So columns 4-7 represent my old date calculation.
Column 8 shows the year that is returned by your suggestion. It's a simple and clever way to flag where the 12/1 date restarts, but I haven't gotten to how to recalculate the actual date from this yet.
I'll lastly add that this basic technique will also work for my model data with 360-day calendars (every month 30 days), though the day and month template will have to be constructed differently since it doesn't ever match the Gregorgian calendar.

Eric Sofen on 29 Jul 2019
Adding support for a broader range of calendars is something I've been thinking about. For a fixed-length calendar (360-day or 365-day), there are a couple issues to deal with:
1) Where do you put the extra days in the Gregorian calendar? Is day 59 from the model on a leap year 29 Feb or 1 Mar? If 29 Feb, then the extra day falls at the end of the year.
2) How do you want to fill in data for the days that aren't represented in the model data? Fill with previous? average of the previous and next? Leave it as missing? This matters when you want to do more aggregation/statistics on your data (e.g. monthly mean and standard deviation).
3) If you're using fraction-of-year values (second column of var_time_mx), you run into floating point and roundoff issues and needing to correct things to the nearest day.
One approach that I've come up with:
d = t.Var1;
dt = datetime(1949,12,1)+calyears(fix(d/365))+caldays(floor(mod(d,365)))+hours((mod(d,365)-floor(mod(d,365)))*24)
This approach includes 29 Feb 1952, but skips 30 Nov 1952 (because 1 Dec is the start of the 365-day year in your definition). This is a bit weird, but a pain to work around and may not be necessary for your use. If it is, you can us the leapyear() function to check whether the dates need to be shifted by one day.

Ellen Maas on 24 Jul 2019
I figured out a brute-force-but-hey-it-works solution for my particular data. I'm not goint to mark this as the Accepted Answer yet, though, in case anyone comes up with a more elegant or general-purpose solution.
Starting with the second file I uploaded ("var_time_mx.txt"), I limited that matrix to 2555 records (since the 2557 of var_time_mx includes two extra days, due to leap years in 1952 and 1956) and put it in a matrix called "sevenyr_mx".
Then I extracted the first 365 records' worth of day-of-month, month number, and day-of-the-year and put that into its own matrix ("days365_mx"). Because the actual date of my full data set starts with 1/1/1950, and that's not a leap year, the date elements will be what I want, repeated every year even in leap years.
Then I copy that 6 times to itself to get 7 years' worth of repeating 365-day-year day, month, and day-of-the-year data.
Then I calculate how many days since 1/1/1950 (not since 12/1/1949), divide that by 365 (per @Walter Roberson in the comments), and strip off the decimals. That gives me the number of years since 1950. Then I just add that to 1950 to get the actual year that goes with this data.
Here's the code:
var_time_mx = importdata('var_time_mx.txt');
sevenyr_mx = var_time_mx(1:2555,:);
days365_mx = var_time_mx(1:365,[5,6,4]);
days365_mx = [days365_mx;repmat(days365_mx,6,1)];
sevenyr_mx(:,9) = sevenyr_mx(:,1)-31.5; %days since 1/1/1950 (not 12/1/1949)
sevenyr_mx(:,10) = floor(sevenyr_mx(:,9)/365);
sevenyr_mx(:,11) = 1950+sevenyr_mx(:,10); % the actual year of the data
sevenyr_mx(:,12:14) = days365_mx; % the actual day, month, and day-of-year data
Columns 4-7 are the dates with the incorrect Gregorian calendar assumption. Columns 11-14 are the dates based on a 365-day calendar.