Handling dates

There are different ways to represent dates. No one way is satisfactory for all purposes. DateTime objects work well for most, if you do not have too many of them. When you do, you will want other representations, according to what you need to get done.

DateTime objects

A .Net DateTime object displays in readable form, and supports the comparison functions and also addition and subtraction. You can include them in arrays with other kinds of data. They have to be serialised to be stored, but that is little trouble when they can represent themselves as readable strings and be as easily reinstantiated from them.

Here are some examples of handling dates as DateTime objects.

      ⎕USING←'System'
      +dt←DateTime.Now             ⍝ displays readably
07/01/2008 16:56:50

      ⍴⍴dt                         ⍝ but is scalar
0

      ⍴dt.⎕NL-2 3                  ⍝ has many properties and methods
59

      ⍴⎕←dt.ToShortDateString      ⍝ represent as character vector
07/01/2008
10

The class offers many useful methods.

      dt.IsDaylightSavingTime
0
      DateTime.Parse∘⊂¨'15/9/52' '15SEP52' '15 September 1952'
 15/09/1952 00:00:00  15/09/1952 00:00:00  15/09/1952 00:00:00
      DateTime.IsLeapYear 2007
0
      DateTime.DaysInMonth¨(2007 2)(2008 2)
28 29

Comparison functions extend to the objects.

      +bd←DateTime.New 1952 9 15   ⍝ SJT’s birthday
15/09/1952 00:00:00

      ⍝ and now his whole family
      ∆←('mum' 1930 5 24)('dad' 1926 9 3)
      ∆,←('caro' 1960 11 16)('jo' 1954 7 5)('me' 1952 9 15)
      +fmly←↑{⍵[1],DateTime.New 1↓⍵}¨∆
 mum   24/05/1930 00:00:00
 dad   03/09/1926 00:00:00
 caro  16/11/1960 00:00:00
 jo    05/07/1954 00:00:00
 me    15/09/1952 00:00:00

      ⊃∘⊃∘⎕CLASS¨fmly[;2]          ⍝ 2nd column contains DateTime objects
 System.DateTime  System.DateTime  System.DateTime  System.DateTime  …

      {⍎⍺,'←⍵'}/fmly               ⍝ associate names and dates

      mum=dad                      ⍝ equivalence
0
      fmly[;2]⍳jo caro             ⍝ lookup
4 3
      mum⌊dad                      ⍝ comparison
03/09/1926 00:00:00
      fmly[⍋fmly[;2];1]            ⍝ names in birth order
 dad  mum  me  jo  caro

Subtraction extends to the objects, returning TimeSpan objects.

      mum-dad                      ⍝ days between my parents' births
1359.00:00:00
      ⊃⊃⎕CLASS mum-dad
System.TimeSpan
      (2-/caro jo me mum dad).Days ⍝ days between all our births
2326 658 8150 1359

For modest amounts of data it is hard to improve on the versatility of DateTime objects.

The obvious limit to this strategy is the number of DateTime objects you create and destroy. When handling large tables and vectors you might need to find lighter, faster ways to handle dates.

That is what this section is about.

Other formats

• Character vectors, such as 15/09/1952 06:30:00, have the advantage of being human readable. This is a solid merit when you are developing with an interpreter and can stop and examine things. This is a useful way to store dates in text files but, given what RDBM systems and XML parsers can do, it is too restrictive for databases or XML documents.

• Numeric vectors: 6- or 7-element numeric vectors that follow the format of the system clock ⎕TS. They are readable, but not convenient for the more common calculations with dates. But where you want access to the parts (years, months and so on) of dates, they can be just what you need. While numeric timestamps might be an awkward format in other languages, Dyalog’s APL roots make them easy to handle.

• Numeric scalars, such as 19520915, are readable and support comparison functions such as = ≠ > < ≤ ≥ ⍒ ⍋ ⌈ ⌊. By using floating-point numbers you can include time of day as well, though you sacrifice some readability. For example, 6.30am on that date would be 19520915.063000. However, you cannot use the important subtract function on dates, though you often want it.

• OA dates and IDNs number the days since an chosen start date. IDNs start from 1 Jan 1900; OA dates (a Microsoft standard used in Office applications) from 31 Dec 1899. These subject your dates to subtraction, but at the cost of readability. As with simple numbers, above, times can be included by using real numbers, and as easily razored off (mnemonic) with the modulus function |.

• Day numbers number the days of the week: 0 through 7. With IDNs, 0s are Sundays; with OA dates, Saturdays.

Switching formats

If you need a lighter representation than DateTime items, you will need some ways of converting between them. We will look first at converting between DateTime objects and other forms, then at converting between the other forms.

Finally, we shall look at algorithms that take advantage of particular representations.

DateTime and character

DateTime objects, being objects, are scalars. They display readably in the session. Applying the format function ⍕ is enough to secure the default display as a character vector.

      +now←DateTime.Now
10/01/2008 18:30:40
      ⍴⍴now
0
      ⍴⎕←⍕now
10/01/2008 18:30:40
19

      ⍴⎕←fmly
 mum   24/05/1930 00:00:00
 dad   03/09/1926 00:00:00
 caro  16/11/1960 00:00:00
 jo    05/07/1954 00:00:00
 me    15/09/1952 00:00:00
5 2
      ⍴⎕←⍕fmly
 mum   24/05/1930 00:00:00
 dad   03/09/1926 00:00:00
 caro  16/11/1960 00:00:00
 jo    05/07/1954 00:00:00
 me    15/09/1952 00:00:00
5 27
      ⍴⎕←⍕¨fmly
 mum   24/05/1930 00:00:00
 dad   03/09/1926 00:00:00
 caro  16/11/1960 00:00:00
 jo    05/07/1954 00:00:00
 me    15/09/1952 00:00:00
5 2

Doesn’t get much easier than that.

DateTime objects also offer methods for specific date formats, such as:

      ⍴¨⎕←(me jo).ToShortDateString
 15/09/1952  05/07/1954
 10  10

These have the usual .Net advantage of honouring the internationalisation settings on the target machine. In similar manner, DateTime can construct instances from character vectors containing a wide range of date formats.

These have the usual .Net advantage of honouring the internationalisation settings on the target machine. In similar manner, <literal>DateTime</literal> can construct instances from character vectors containing a wide range of date formats.

Numeric vectors

This could hardly be easier.

      ⍴⎕←me.(Year Month Day)
1952 9 15
3
      ⍴⎕←me.(Year Month Day Hour Minute Second)
1952 9 15 0 0 0
6
      DateTime.New 1952 9 15
15/09/1952 00:00:00
      DateTime.New 1952 9 15 6 30 0
15/09/1952 06:30:00
      me=DateTime.New 1952 9 15
1
      fmly[;,1],(fmly[;2]).(Year Month Day)
 mum   1930 5 24
 dad   1926 9 3
 caro  1960 11 16
 jo    1954 7 5
 me    1952 9 15

Numeric scalars

DateTime objects don’t offer this representation so we convert to and from numeric vectors.

      100⊥1952 9 15
19520915
      100⊥1952 9 15 6 30 0
1.952091506E13
      0 100 100⊤19520915
1952 9 15
      0 100 100 100 100 100⊤19520915063000
1952 9 15 6 30 0

Note that while we need only a scalar left argument to the encode ⊥ function, we need a vector for its inverse, the decode ⊤ function. That left argument might become a little unwieldy. It could be more convenient to define ed to encode dates and dd as its inverse.

      ed←0 100 100∘⊥
      dd←ed⍣¯1
      ed 1952 9 15 6 30 0
19520915
      dd 19520915063000
1952 9 15
      fmly[;,1],ed¨(fmly[;2]).(Year Month Day)
 mum   19300524
 dad   19260903
 caro  19601116
 jo    19540705
 me    19520915

Similarly et and dt for timestamps.

      et←÷∘1e6∘(0 100 100 100 100 100∘⊥)
      dt←et⍣¯1
      et 1952 9 15 6 30 0
19520915.06
      6⍕et 1952 9 15 6 30 0
 19520915.063000

OA Dates and IDNs

DateTime objects provide methods to convert to and from OA dates.

      me.ToOADate
19252
      DateTime.FromOADate 19252
15/09/1952 00:00:00
      fmly[;,1],(fmly[;2]).ToOADate
 mum   11102
 dad    9743
 caro  22236
 jo    19910
 me    19252

Watch out: these look like but differ from IDNs. Microsoft’s day count starts one day earlier than the international standard.

      (DateTime.New 1952 9 15).ToOADate
19252
      #.DateToIDN 1952 9 15
19251
      DateTime.FromOADate 19252.5
15/09/1952 12:00:00
      #.IDNToDate 19251.5
1952 9 15 0

OA Dates are exactly what you want for Microsoft applications such as Excel. They even handle fractional dates correctly. Otherwise you can use the light and fast Dyalog root methods #.DateToIDN and #.IDNToDate.

      fmly[;,1],#.DateToIDN¨(fmly[;2]).(Year Month Day)
 mum   11101
 dad    9742
 caro  22235
 jo    19909
 me    19251

Converting between other formats

Where speed matters you will avoid creating large arrays of DateTime objects. But different representations suit different purposes.

attachment:dateformats.jpg

To summarise:

• If you can use .Net and do not to have to create prohibitively long arrays of them, will you find DateTime objects most convenient.

• If you have to parse or produce varied character date formats, especially in internationalised forms, you will find the DateTime class convenient, even if you restrict yourself to its static methods and do not create arrays of DateTime objects.

• If you are interfacing to Microsoft applications such as Excel you will find OA dates convenient. You can create these without invoking the DateTime class at all, by subtracting 1 from IDNs.

• If you need speed, or independence from .Net, stay on the right-hand side of the figure.

Here is an example of switching between representations to simplify calculation.

Calculating the next anniversary of a date is easily done in vector format.

      ⍝ next anniv of ⍵[2] after ⍵[1] (y m d)
      nxtann←{(3↑>/10000⊤100⊥⍉⍵)+⊃↓0 1 1⊖⍵}∘{↑¯3↑¨⍵}
      nxtann (2008 12 23)(12 25)
2008 12 25
      nxtann (2008 12 27)(12 25)
2009 12 25

Differences are best calculated between IDNs.

      onIDNs←{⊃⍺⍺/#.DateToIDN¨⍺ ⍵}     ⍝ ⍺⍺ on args as IDNs
      2008 12 25 -onIDNS 2008 1 11     ⍝ days to Xmas
349

For shopping days, we count the days that are not Sundays.

      wnb←{⍺+⍳⍵-⍺+1}                   ⍝ whole numbers between
      3 wnb 9
4 5 6 7 8
      sd←{×7|⍵}                        ⍝ shopping days (IDNs)

      sd2x←{+/sd ⍺ wnb onIDNs nxtann ⍺ ⍵}∘12 25
      sd2x 2008 1 11
298

Some simple techniques for working with dates.

Addition and subtraction on dates are conveniently performed as IDN numbers. The root functions DateToIDN and IDNToDate are fast and handy:

      -/DateToIDN¨(1954 7 5)(1952 9 15) ⍝ days between my and my sister's births
658

But we'll get tired of seeing them everywhere. So let's have an operator.

      asIDN←{IDNToDate ⍺ ⍺⍺ DateToIDN ⍵}
      7 -⍨ asIDN 1954 7 5 ⍝ 1 week before my sister's birthday
1954 6 28 0

Scalars are often more convenient than triples. We can use ⊥ and ⊤ to switch representations.

      100⊥1954 7 5
19540705
      0 100 100⊤19540705
1954 7 5
      (100⊥⍣¯1)19540705
1954 7 5
      dual←{⍺(⍺⍺⍣¯1)⍵⍵ ⍺ ⍺⍺ ⍵}
      0 100 100⊤dual {⎕←⍵} 19540705                           ⍝ display before recoding
1954 7 5
19540705
      0 100 100⊤dual {⎕←⍵} 19540705 19520915                  ⍝ my and my sister's birthdates
1954 1952
   7    9
   5   15
19540705 19520915
      0 100 100⊤dual {⎕←⍵} 20070714 19520915                  ⍝ today and my birthdate
2007 1952
   7    9
  14   15
20070714 19520915
      0 10000⊤dual {⎕←⍵} 20070714 19520915                    ⍝ split ccyy mmdd instead of ccyy mm dd
2007 1952
 714  915
20070714 19520915
      0 10000⊤dual {1 1⍉⎕←⍵} 20070714 19520915                ⍝ my birthday this year
2007 1952
 714  915
20070915
      0 10000⊤dual {1 1⍉⎕←⍵} 20071014 19520915                ⍝ but perhaps already passed
2007 1952
1014  915
20070915
      0 10000⊤dual {(2↑2⊃>/⍵)+1 1⍉⎕←⍵} 20071014 19520915      ⍝ my next birthday
2007 1952
1014  915
20080915
      nextbirthday←{0 10000⊤dual {(2↑2⊃>/⍵)+1 1⍉⍵} ⍺ ⍵}       ⍝ as a dyadic fn

      20070714 nextbirthday¨19520915 19540705                 ⍝ my and my sister's next birthdays
20070915 20080705
      Jo SJT KEI Jesus←0705 0915 1217 1225                    ⍝ birth years optional
      20070714 nextbirthday¨Jo SJT KEI Jesus
20080705 20070915 20071217 20071225

      YMDToIDN←{DateToIDN 0 100 100∘⊤⍵}                       ⍝ convert ccyymmdd to IDN
      asIDN←{⊃⍺⍺/YMDToIDN¨⍺ ⍵}                                ⍝ perform ⍺⍺ on args as IDNs
      20070915 -asIDN 20070714                                ⍝ days to my next birthday
63
      20070714{⍺ -⍨asIDN ⍺ nextbirthday ⍵}1225                ⍝ days to Christmas
164

      nb←{⍺+⍳⍵-⍺+1}                                           ⍝ whole numbers between args
      wkday←{(7|⍵)∊⍳5}                                        ⍝ is arg a weekday?

      20070714{+/wkday ⍺ nb asIDN ⍺ nextbirthday ⍵}Jesus      ⍝ count the weekdays to Christmas
116