Initializing help system before first use

Handling dates and time

Fields of databases that are defined as date or time types find their direct correspondence in the types date, time, or datetime of the Mosel module mmsystem. The modules mmodbc, mmoci and mmsheet support these types for reading and writing data and we explain here how to work with them.

Dates and times are passed in their textual representation from a database to Mosel (or from Mosel to the database). The representation of date and time information within databases is different from one product to another and may not be compatible with Mosel's default format. The first step when starting to work with date and time related data therefore always is to retrieve sample data in the form of a string and print it out to analyze its format. This can be done by a few lines of Mosel code, such as: 

 declarations
  sd,st: string
 end-declarations

 initializations from "datetest.dat"
  sd as "ADate"
  st as "ATime"
 end-initializations

 writeln("sd: ", sd, ", st: ", st)

The date and time formats are defined by setting the parameters timefmt, datefmt, and datetimefmt of module mmsystem. The encoding of the format strings is documented in the 'Mosel Language Reference Manual', Chapter 'mmsystem'.

In the model displayed below we read a first set of dates/times that are defined as such in the data source. The second set are simply strings in the data source and Mosel transforms them into dates/times according to the format defined by our model before reading the data. For the output we use Mosel's own format; depending on the data source the result will be interpreted as strings or as time/date data. 

model "Dates and times (ODBC)"
 uses "mmsystem", "mmodbc"

 declarations
  T: time
  D: date
  DT: datetime
  Dates: list of date
 end-declarations

! Select the format used by the spreadsheet/database
! (database fields have date/time types)
 setparam("timefmt", "%y-%0m-%0d %0H:%0M:%0S")
 setparam("datefmt", "%y-%0m-%0d")
 setparam("datetimefmt", "%y-%0m-%0d %0H:%0M:%0S")

 initializations from "mmodbc.odbc:datetime.mdb"
  T as "Time1"
  D as "Date1"
  DT as "DateTime1"
  Dates as "Dates"
 end-initializations

 setparam("timefmt", "%h:%0M %p")
 writeln(D, ", ", T)
 writeln(DT)
 writeln(Dates)

! Read date / time from strings (database fields have some string type)
 setparam("timefmt", "%Hh%0Mm")
 setparam("datefmt", "%dm%0my%0y")
 setparam("datetimefmt", "%dm%0my%0y, %Hh%0Mm")

 initializations from "mmodbc.odbc:datetime.mdb"
  T as "Time2"
  D as "Date2"
  DT as "DateTime2"
 end-initializations

 writeln(D, ", ", T)
 writeln(DT)

! Use Mosel's default format
 setparam("timefmt", "")
 setparam("datefmt", "")
 setparam("datetimefmt", "")

 writeln(D, ", ", T)
 writeln(DT)

! The following assumes that the database output fields have type string
! since we are not using the date/time formatting expected by the database
 initializations to "mmodbc.odbc:datetime.mdb"
  T as "TimeOut"
  D as "DateOut"
  DT as "DateTimeOut"
 end-initializations
end-model

The formatting for dates and times at the beginning of the model where we read database fields with date/time types (Time1, Date1, DateTime1, and Dates) applies to Access and Excel read through ODBC. For an SQLite or mysql database this would be 

 setparam("timefmt", "%0H:%0M:%0S")
 setparam("datefmt", "%y-%0m-%0d")
 setparam("datetimefmt", "%y-%0m-%0d %0H:%0M:%0S")

and an Oracle database uses the following format:

 setparam("timefmt", "%0d-%N-%0Y %0h.%0M.%0S.%0s %P")
 setparam("datefmt", "%0d-%N-%0Y")
 setparam("datetimefmt", "%0d-%N-%0Y %0h.%0M.%0S.%0s %P")

The xls and xlsx drivers receive dates, times and timestamps in encoded form, the conversion to the text form always uses the default format of mmsystem. This means that we can simply leave out the setparam calls at the beginning of the model, or explicitly reset the parameters to their default values with 

 setparam("timefmt", "")
 setparam("datefmt", "")
 setparam("datetimefmt", "")

When using the excel driver for accessing Excel spreadsheets we need to be careful when reading times since these are passed as a real value that needs to be converted to Mosel's representation of times (the second half of the model working with strings remains unchanged). 

 declarations
  T: time
  D: date
  DT: datetime
  Dates: list of date
  r: real
 end-declarations

! Select the format used by the spreadsheet
 setparam("timefmt", "%0h:%0M:%0S %P")
 setparam("datefmt", "%0m/%0d/%y")
 setparam("datetimefmt", "%0d/%0m/%y %0H:%0M:%0S")

 initializations from 'mmsheet.excel:datetime.xls'
  r as "skiph;Time1"                ! Time is stored as a real
  D as "skiph;Date1"
  DT as "skiph;DateTime1"
  Dates as "skiph;Dates"
 end-initializations

 T:=time(round(r*24*3600*1000))
 writeln(D, ", ", T)
 writeln(DT)
 writeln(Dates)

For the csv driver only the second part of the model (reading from strings) is relevant since date and time values in CSV format files are always encoded as strings.

Our model implemented with SQL statements looks as follows.

model "Dates and times (SQL)"
 uses "mmsystem", "mmodbc"

 declarations
  T,: time
  D: date
  DT: datetime
  Dates: list of date
 end-declarations

 setparam("SQLverbose",true)
 SQLconnect("datetime.mdb")

! Select the format used by the spreadsheet/database
! (database fields have date/time types)
 setparam("timefmt", "%y-%0m-%0d %0H:%0M:%0S")
 setparam("datefmt", "%y-%0m-%0d")
 setparam("datetimefmt", "%y-%0m-%0d %0H:%0M:%0S")

 T:=time(SQLreadstring("select * from Time1"))
 D:=date(SQLreadstring("select * from Date1"))
 DT:=datetime(SQLreadstring("select * from DateTime1"))
 SQLexecute("select * from Dates", Dates)

 setparam("timefmt", "%h:%0M %p")
 writeln(D, ", ", T)
 writeln(DT)
 writeln(Dates)

! Read date / time from strings (database fields have some string type)
 setparam("timefmt", "%Hh%0Mm")
 setparam("datefmt", "%dm%0my%0y")
 setparam("datetimefmt", "%dm%0my%0y, %Hh%0Mm")

 T:=time(SQLreadstring("select * from Time2"))
 D:=date(SQLreadstring("select * from Date2"))
 DT:=datetime(SQLreadstring("select * from DateTime2"))

 writeln(D, ", ", T)
 writeln(DT)

! Use Mosel's default format
 setparam("timefmt", "")
 setparam("datefmt", "")
 setparam("datetimefmt", "")

 writeln(D, ", ", T)
 writeln(DT)

 SQLexecute("delete from TimeOut")      ! Cleaning up previous results: works
 SQLexecute("delete from DateOut")      ! only for databases, cannot be used
 SQLexecute("delete from DateTimeOut")  ! with spreadsheets (instead, delete
                                        ! previous solutions directly in the
                                        ! spreadsheet file)
 SQLexecute("insert into TimeOut values (?)", [T])
 SQLexecute("insert into DateOut values (?)", [D])
 SQLexecute("insert into DateTimeOut values (?)", [DT])

 SQLdisconnect
end-model

Parent Topic Examples