Date and time data types

The module mmsystem of the standard distribution of Mosel defines the types date (calendar day: day, month, and year), time (time of the day in milliseconds), and datetime (combination of the first two) for working with date and time related data in Mosel models. We show here some examples of

• reading and writing dates and times from/to file,
• formatting dates and times,
• transformation from/to the underlying numerical representation,
• applying operations (comparison, addition, difference),
• enumerating dates and times.

Initializing dates and times

The following line prints out the current date and time (using the default format):

 writeln("Today: ", date(SYS_NOW), ", current local time: ", time(SYS_NOW),
         "UTC time: ", gettime(datetime(timestamp)) )

When we wish to read data from a file, the formatting of dates and times needs to be adapted to the format used in the file. For example, consider the following data file (datetime.dat)

Time1:  "4pm"
Time2:  "16h00"
Time3:  "16:00:00"

Date1:  "2-20-2002"
Date2:  "20/02/02"
Date3:  "20-Feb-2002" 

A Mosel model reading in this data file may look thus (file dates.mos).

 declarations
  t: time
  d: date
 end-declarations

 setparam("timefmt", "%h%p")             ! h: hours in 1-12, p: am/pm
 setparam("datefmt", "%m-%d-%y")         ! m: month, d: day, y: year

 initializations from "datetime.dat"
  t as "Time1"
  d as "Date1"
 end-initializations

 writeln(d, ", ", t)

 setparam("timefmt", "%Hh%0M")           ! H: hours in 0-23, M: minutes
 setparam("datefmt", "%0d/%0m/%0Y")      ! Y: year in 0-99
                                         ! 0: fill spaces with '0'

 initializations from "datetime.dat"
  t as "Time2"
  d as "Date2"
 end-initializations

 writeln(d, ", ", t)

 setparam("timefmt", "%H:%0M:%0S")       ! S: seconds
 setparam("datefmt", "%d-%N-%y")         ! N: use month names

 initializations from "datetime.dat"
  t as "Time3"
  d as "Date3"
 end-initializations

 writeln(d, ", ", t) 

For the encoding of date and time format strings please refer to the documentation of the parameters datefmt and timefmt in the 'Mosel Language Reference Manual'.

Date3 in this example uses a month name, not a number. The default 3-letter abbreviations of month names can be changed (e.g., translated) by redefining the parameter monthnames. For instance, a date written in French, such as

Date4:  "20 fevrier 2002" 

is read by the following Mosel code:

 setparam("datefmt", "%d %N %y")

 setparam("monthnames", "janvier fevrier mars avril mai juin juillet " +
          "aout septembre octobre novembre decembre")

 initializations from "datetime.dat"
  d as "Date4"
 end-initializations

 writeln(d) 

In the examples of this section we have used Mosel's standard text format for reading and writing dates and times. These data types can also be used when accessing spreadsheets or databases through Mosel's ODBC connection or the software-specific interfaces for Oracle and MS Excel. The whitepaper Using ODBC and other database interfaces with Mosel documents some examples of accessing date and time data in spreadsheets and databases.

Note: When initializing or constructing dates Mosel does not control whether they correspond to an actual calendar day (e.g., 0-valued or negative day and month counters are accepted). The validity of a date or time can be tested with the function isvalid. For example, the following code extract

 d:= date(2000,0,0)
 writeln(d, " is a valid date: ", if(isvalid(d), "true", "false")) 

results in this output:

2000-00-00 is a valid date: false 

Conversion to and from numbers

In some cases it might be necessary to use the numerical representation in the place of a date or time. In the following Mosel extract we wish to define an array YEARS that is indexed by a set of dates. Since it is not possible to use the type 'date' for an index we work with the numerical representation obtained by applying getasnumber to the dates (this function returns an integer, the Julian Day number = number of days elapsed since 1/1/1970; if the argument is a time getasnumber returns the number of milliseconds since midnight). By applying date to the numerical representation it is converted back to the original date.

With this Mosel code

 declarations
  Dates: set of date
  YEAR: array(NDates: set of integer) of integer
 end-declarations

 setparam("datefmt", "")                 ! Use the default format

 initializations from "datetime.dat"
  Dates
 end-initializations

 writeln("Dates: ", Dates)
 forall(dd in Dates) YEAR(getasnumber(dd)):= getyear(dd)
 writeln("YEAR: ", YEAR)
 forall(n in NDates) writeln(date(n))    ! Mapping back to original dates

and the following data

Dates: [ "1999-1-21" "2000-2-22" "2002-3-23" "2005-4-24" "2010-5-25"] 

we obtain this output:

Dates: {1999-01-21,2000-02-22,2002-03-23,2005-04-24,2010-05-25}
YEAR: [(10612,1999),(11009,2000),(11769,2002),(12897,2005),(14754,2010)]
1999-01-21
2000-02-22
2002-03-23
2005-04-24
2010-05-25 

Similarly to what is shown here, function getasnumber can be used with 'time' and 'datetime', the backwards conversion being carried out by time or datetime respectively.

Operations and access functions

The following Mosel model extract (dates.mos) shows some operations on dates and times, including difference between two dates or times and addition of constants to obtain an enumeration of dates or times.

 declarations
  t: time
  d: date
  now1,now2: datetime
  DNAMES: array(1..7) of string
 end-declarations

! Difference between dates
 writeln("February 2004 had ", date(2004,3,1)-date(2004,2,1), " days.")

! Retrieve the weekday
 DNAMES:: (1..7)["Monday", "Tuesday", "Wednesday", "Thursday", "Friday",
                 "Saturday", "Sunday"]
 writeln("1st January 2000 was a ", DNAMES(getweekday(date(2000,1,1))))

! Difference between times
 now1:= datetime(SYS_NOW)
 wait(1)                                 ! Delay model execution for 1 second
 now2:= datetime(SYS_NOW)
 writeln("Elapsed time: ", now2-now1, "ms")

! Enumeration / addition to 'time'
 setparam("timefmt", "%.h.%0M%p")
 t:= time(11,0)
 forall(i in 1..5) do
  writeln(t)
  t+=30*60*1000                          ! Add 30 minutes
 end-do

! Enumeration / addition to 'date'
 setparam("datefmt", "%.d/%0m/%0Y")
 d:= date(2005,12,20)
 forall(i in 1..5) do
  writeln(d)
  d+=14                                  ! Add 14 days
 end-do 

Executing this model produces the following output.

February 2004 had 29 days.
1st January 2000 was a Saturday
Elapsed time: 1.006ms
11.00am
11.30am
12.00pm
12.30pm
 1.00pm
20/12/05
 3/01/06
17/01/06
31/01/06
14/02/06