Loren on the Art of MATLAB

Turn ideas into MATLAB

MATLAB Data Types for Dates and Time, Part II 2

Posted by Loren Shure,

Once again we're going to hear from guest blogger Andrea Ho, who works for the MATLAB Documentation team here at MathWorks.

Contents

Last time, I introduced new data types for representing date and time data in MATLAB: datetime for representing specific points in time, duration for representing exact differences between points in time, and calendarDuration for representing flexible amounts of time such as months. Today, we'll look at how these data types help you to work with time zones, daylight saving time, and dates in languages other than English.

Interest Payments from a Swiss Bank

Last time, we looked at bank interest payments over the course of a year, without paying attention to the bank location. Let's do a similar analysis, but this time, our bank is located in Switzerland and we are in Phoenix, Arizona.

Suppose we opened a bank account on January 1, 2013, and the account pays interest monthly on the last day of each month. Create a sequence of dates in 2013 to represent when the bank account pays interest. The dateshift function can shift a date and create a sequence based on the initial date in one line.

p0 = datetime(2013,1,1);
p = dateshift(p0,'end','month',0:11)'
p = 
   31-Jan-2013
   28-Feb-2013
   31-Mar-2013
   30-Apr-2013
   31-May-2013
   30-Jun-2013
   31-Jul-2013
   31-Aug-2013
   30-Sep-2013
   31-Oct-2013
   30-Nov-2013
   31-Dec-2013

Suppose our records indicate that we received interest payments at either 3 AM or 4 AM, depending on the month of the year. We can input this information to our datetime vector p, by modifying its Hour property.

p.Hour = [4 4 3 3 3 3 3 3 3 4 4 4]';
p.Format = 'dd-MMM-yyyy HH:mm'
p = 
   31-Jan-2013 04:00
   28-Feb-2013 04:00
   31-Mar-2013 03:00
   30-Apr-2013 03:00
   31-May-2013 03:00
   30-Jun-2013 03:00
   31-Jul-2013 03:00
   31-Aug-2013 03:00
   30-Sep-2013 03:00
   31-Oct-2013 04:00
   30-Nov-2013 04:00
   31-Dec-2013 04:00

Our bank in Zurich, Switzerland, has sent us a file named interest_2013.txt that contains interest amounts for each month of the year.

For English-language dates and times, you can use the Import Tool to import your data graphically. However, the dates in our file are written in German:

Date,Interest
31. Januar 2013 12:00,1.93
28. Februar 2013 12:00,4.28
31. März 2013 12:00,4.82
30. April 2013 12:00,1.23
31. Mai 2013 12:00,5.89
30. Juni 2013 12:00,2.26
31. Juli 2013 12:00,3.84
31. August 2013 12:00,5.82
30. September 2013 12:00,2.51
31. Oktober 2013 12:00,2.99
30. November 2013 12:00,6.17
31. Dezember 2013 12:00,2.65

For non-English language dates, we must import the data using functions. We will use readtable to read the file data into a table. Use the %D specifier to read the first column of data as datetime values and specify the format of the date strings within curly braces. The 'DateLocale' name-value pair argument tells MATLAB how to interpret the date strings in the file. The value of DateLocale is a combination of a lowercase language code and an uppercase country code. For example, 'de_CH' indicates German-language dates in Switzerland.

T = readtable('interest_2013.txt',...
    'Format','%{dd.MMMM yyyy HH:mm}D %f','DateLocale','de_CH')
T = 
             Date              Interest
    _______________________    ________
    31.January 2013 12:00      1.93    
    28.February 2013 12:00     4.28    
    31.March 2013 12:00        4.82    
    30.April 2013 12:00        1.23    
    31.May 2013 12:00          5.89    
    30.June 2013 12:00         2.26    
    31.July 2013 12:00         3.84    
    31.August 2013 12:00       5.82    
    30.September 2013 12:00    2.51    
    31.October 2013 12:00      2.99    
    30.November 2013 12:00     6.17    
    31.December 2013 12:00     2.65    

It appears that the bank's payments were made at noon each month, while our own records indicate otherwise. What happened? Our original times are based in Phoenix, whereas the imported times from the bank are based in Zurich.

By default, datetime values are not associated with any time zone. That is, they are "unzoned". Because we want to compare dates and times across geographic locations and therefore time zones, we should associate each datetime array with a time zone. You can learn more about time zones here.

Let's set the time zone for our Phoenix-based datetime vector, p. Valid values for the TimeZone property include names of time zone regions from the IANA Time Zone Database. Then, adjust the display format to show the UTC offset for each value.

p.TimeZone = 'America/Phoenix';
p.Format = 'dd-MMM-yyyy HH:mm Z'
p = 
   31-Jan-2013 04:00 -0700
   28-Feb-2013 04:00 -0700
   31-Mar-2013 03:00 -0700
   30-Apr-2013 03:00 -0700
   31-May-2013 03:00 -0700
   30-Jun-2013 03:00 -0700
   31-Jul-2013 03:00 -0700
   31-Aug-2013 03:00 -0700
   30-Sep-2013 03:00 -0700
   31-Oct-2013 04:00 -0700
   30-Nov-2013 04:00 -0700
   31-Dec-2013 04:00 -0700

Unlike most of the United States, Phoenix does not observe daylight saving time and is always 7 hours behind UTC.

Now, specify that the bank's payment times are in Zurich's time zone.

z = T.Date;
z.TimeZone = 'Europe/Zurich';
z.Format = 'dd-MMM-yyyy HH:mm Z'
z = 
   31-Jan-2013 12:00 +0100
   28-Feb-2013 12:00 +0100
   31-Mar-2013 12:00 +0200
   30-Apr-2013 12:00 +0200
   31-May-2013 12:00 +0200
   30-Jun-2013 12:00 +0200
   31-Jul-2013 12:00 +0200
   31-Aug-2013 12:00 +0200
   30-Sep-2013 12:00 +0200
   31-Oct-2013 12:00 +0100
   30-Nov-2013 12:00 +0100
   31-Dec-2013 12:00 +0100

In Zurich, the time zone offset changes during the year because Zurich observes daylight saving time.

Now if we compare our payment times with the bank's data, we'll see that they are the same.

isequal(p,z)
ans =
     1

Not Every Day Has 24 Hours

On what day of the week were interest payments made? We can use the day function to extract the day name from each value in z.

d = day(z,'name')
d = 
    'Thursday'
    'Thursday'
    'Sunday'
    'Tuesday'
    'Friday'
    'Sunday'
    'Wednesday'
    'Saturday'
    'Monday'
    'Thursday'
    'Saturday'
    'Tuesday'

The third payment date (March 31) falls on a Sunday. Suppose the bank shifted this payment date earlier by two days so that it would not fall on a weekend.

What happens if we subtract a duration of 2 standard, 24-hour long days to the corresponding payment date?

new_date = z(3) - days(2)
new_date = 
   29-Mar-2013 11:00 +0100

Notice that the new payment time is now 11 AM instead of noon because a daylight saving time shift occurred in Zurich on March 30, 2014.

To account for a daylight saving time shift in a calendar calculation, we should subtract 2 calendar days from the original payment date.

new_date = z(3) - caldays(2)
new_date = 
   29-Mar-2013 12:00 +0100

Now the payment time is at noon, and is consistent with the payment times during the rest of the year. If we calculate the exact duration between the original payment date and the new payment date, we will see that the difference is not 48 hours (two 24-hour long days). Rather, the difference is 47 hours due to the daylight saving time change.

new_date - z(3)
ans = 
   -47:00:00

Here's the big takeaway: When a datetime value is associated with a time zone that observes daylight saving time, it is affected by daylight saving time changes. To correctly account for such time changes, make sure you use calendar durations instead of durations in calendar calculations involving days, months, or years. calendarDuration values account for non-constant lengths of time such as the length of a day, which is not always equal to 24 hours in some time zones.

Quarterly Statistics

Now we will calculate the mean of the interest payments for each quarter. Just like how the day function can extract day names for each datetime value in an array, the quarter function lets us extract the quarter number.

q = quarter(z)
q =
     1
     1
     1
     2
     2
     2
     3
     3
     3
     4
     4
     4

Now we can calculate statistics for the interest payments associated with each unique value of of q.

for ii = 1:4
    X = sprintf('Quarter %d',ii);
    disp(X)
    tf = q==ii;
    m_quarter = mean(T.Interest(tf))
    % T.Interest is a column containing interest values from
    % the text file we imported earlier
end
Quarter 1
m_quarter =
       3.6767
Quarter 2
m_quarter =
       3.1267
Quarter 3
m_quarter =
       4.0567
Quarter 4
m_quarter =
       3.9367

Convert Date Numbers to Datetime

Now we've seen how to create datetime values and how to import date and time data as datetime values that can account for time zones and daylight saving time. But what if you currently have data in the form of serial date numbers? Fear not, there's an easy way to convert your existing data to more convenient datetime values. Let's start with an array of serial date numbers:

dn = (735600:31:735755)'
dn =
      735600
      735631
      735662
      735693
      735724
      735755

Use the datetime function to easily convert the date numbers to datetime values.

p = datetime(dn,'ConvertFrom','datenum')
p = 
   01-Jan-2014 00:00:00
   01-Feb-2014 00:00:00
   04-Mar-2014 00:00:00
   04-Apr-2014 00:00:00
   05-May-2014 00:00:00
   05-Jun-2014 00:00:00

An array of serial date numbers cannot account for a time zone, but you can add time zone information to the datetime array.

p.TimeZone = 'America/New_York';

You can even export the datetime array in a different language. Let's translate the dates into German and then export them to a text file.

C = cellstr(p,'dd. MMMM yyyy','de_DE')
C = 
    '01. Januar 2014'
    '01. Februar 2014'
    '04. März 2014'
    '04. April 2014'
    '05. Mai 2014'
    '05. Juni 2014'
T  = table(C,rand(6,1),'VariableNames',{'Date','InterestRate'})
T = 
           Date           InterestRate
    __________________    ____________
    '01. Januar 2014'      0.2785     
    '01. Februar 2014'    0.54688     
    '04. März 2014'       0.95751     
    '04. April 2014'      0.96489     
    '05. Mai 2014'        0.15761     
    '05. Juni 2014'       0.97059     
writetable(T,'myfile.txt')

Your Thoughts?

Have you tried using the datetime, duration, and calendarDuration data types? Let us know what you think by leaving a comment here.


Get the MATLAB code

Published with MATLAB® R2014b

6 views (last 30 days)  | |

Comments

To leave a comment, please click here to sign in to your MathWorks Account or create a new one.