SQLITE Date/Time Tips

Here are some tips where the embedded SQLite database engine in the runtimes can enhance the PL/B language operations regarding date/time functions.

SQLite Date And Time Functions

By creating an in-memory database, the programmer can get quick and easy access to the SQLite date and time functions. Access to an in-memory database using SQLite can be achieved using the DBCONNECT instruction as follows:

SQLite supports five date and time functions as follows:

1. date(timestring, modifier, modifier, ...)
   
   The date() function returns the date as YYYY-MM-DD. 

2. time(timestring, modifier, modifier, ...)
   
   The time() function returns the time as HH:MM:SS. 

3. datetime(timestring, modifier, modifier, ...)
   
   The datetime() function returns YYYY-MM-DD HH:MM:SS. 

4. julianday(timestring, modifier, modifier, ...)
   
   The julianday() function returns the Julian day that is the number of days since noon in Greenwich on November 24, 4714 B.C. 

5. strftime(format, timestring, modifier, modifier, ...)
   
   The strftime() function returns the date formatted according to a format string specified by the first argument to the function. 

 

These SQLite functions can be specified in a SQL SELECT statement to perform the desired SQL operation to retrieve the desired data.

 

All five date and time functions take a time string as an argument. The time string is followed by zero or more modifiers. The strftime() function also takes a format string as its first argument. More information about these functions is available as described by the SQLite documentation at the 'www.sqlite.org' web site.

 

The 'timestring' argument in the these functions can be followed by zero or more modifiers that alter date and/or time. Each modifier is a transformation that is applied to the time value to its left. Modifiers are applied from left to right; order is important. The available modifiers are as follows.

     

NNN days

NNN hours

NNN minutes

NNN.NNNN seconds

NNN months

NNN years

start of month

start of year

start of day

weekday N

Linuxepoch

localtime

utc

 

The first six modifiers (1 through 6) simply add the specified amount of time to the date and time specified by the preceding timestring and modifiers. Note that "±NNN months" works by rendering the original date into the YYYY-MM-DD format, adding the ±NNN to the MM month value, then normalizing the result. For example, the data 2001-03-31 modified by '+1 month' initially yields 2001-04-31, but April only has 30 days so the date is normalized to 2001-05-01. A similar effect occurs when the original date is February 29 of a leap year and the modifier is ±N years where N is not a multiple of four.

 

The "start of" modifiers (7 through 9) shift the date backwards to the beginning of the current month, year or day. The "weekday" modifier advances the date forward to the next date where the weekday number is N Sunday is 0, Monday is 1, and so forth.

 

The "Linuxepoch" modifier (11) only works if it immediately follows a timestring in the DDDDDDDDDD format. This modifier causes the DDDDDDDDDD to be interpreted not as a Julian day number as it normally would be, but as Linux Time - the number of seconds since 1970. If the "Linuxepoch" modifier does not follow a timestring of the form DDDDDDDDDD which expresses the number of seconds since 1970 or if other modifiers separate the "Linuxepoch" modifier from prior DDDDDDDDDD then the behavior is undefined. Due to precision limitations imposed by the implementations use of 64-bit integers, the "Linuxepoch" modifier only works for dates between 0000-01-01 00:00:00 and 5352-11-01 10:52:47 (Linux times of -62167219200 through 10675199167).

 

The "localtime" modifier (12) assumes the time string to its left is in Universal Coordinated Time (UTC) and adjusts the time string so that it displays localtime. If "localtime" follows a time that is not UTC, then the behavior is undefined. The "utc" is the opposite of "localtime". "utc" assumes that the string to its left is in the local timezone and adjusts that string to be in UTC. If the prior string is not in localtime, then the result of "utc" is undefined.

 

Example Program Using SQLite Date/Time Functions:

 

 

Conn	DBFILE
F1	FORM	"10"
	Data Dim	40
	DbConnect	Conn, "SQLITE;;:memory:","",""
	Keyin	*ES,"Test SQLite date functions: ",Ans
.
	Display	"Compute the current date. ";
	DbSend	Conn;"SELECT date('now')"
	DbExecute	Conn
	Dbfetch	Conn,F1;Data
	Display	Data
.
	Display	"Compute the last day of the current month."
	DbSend	Conn;"SELECT date('now','start of ":
		"month','+1 month','-1 day')"
	DbExecute	Conn
	Dbfetch	Conn,F1;Data
	Display	Data
.
	Display	"Compute the date and time given a Linux timestamp 1092941466."
	DbSend	Conn;"SELECT datetime(1092941466, 'Linuxepoch')"
	DbExecute	Conn
	Dbfetch	Conn,F1;Data
	Display	Data
.
	Display	"Compute the date and time given a Linux ":
		"timestamp 1092941466, and compensate for ":
		"your local timezone."
	DbSend	Conn;"SELECT datetime(1092941466, ":
		"'Linuxepoch', 'localtime')"
	DbExecute	Conn
	Dbfetch	Conn,F1;Data
	Display	Data
	...
	Display	"Compute the current Linux timestamp."
	DbSend	Conn;"SELECT strftime('%s','now')"
	DbExecute	Conn
	Dbfetch	Conn,F1;Data
	Display	Data
.
	Display	"Compute the number of days since the ":
		"signing of the US Declaration of ":
		"Independence."
	DbSend	Conn;"SELECT julianday('now') - ":
		"julianday('1776-07-04')"
	DbExecute	Conn
	Dbfetch	Conn,F1;Data
	Display	Data
.
	Display	Compute the number of seconds since a ":
		"particular moment in 2004:"
	DbSend	Conn;"SELECT strftime('%s','now') - ":
		"strftime('%s','2004-01-01 ":
		"02:34:56')"
	DbExecute	Conn
	Dbfetch	Conn,F1;Data
	Display	Data
.
	Display	"Compute the date of the first Tuesday in ":
		"October for the current year."
	DbSend	Conn;"SELECT date('now','start of ":
		"year','+9 months','weekday 2')"
	DbExecute	Conn
	Dbfetch	Conn,F1;Data
	Display	Data
.
	Display	"Compute the time since the Linux epoch in ":
		"seconds (like strftime('%s','now') except ":
		"includes fractional part):"
	DbSend	Conn;"SELECT (julianday('now') - ":
		"2440587.5)*86400.0"
	DbExecute	Conn
	Dbfetch	Conn,F1;Data
	Display	Data
.
	Keyin Ans
	DbDisconnect	Conn
	Stop

 

Example Program Using SQLite Date/Time Functions:

 

.Sample code to update a date by 10 days in a file.

 

Conn	DBFILE
.
	DbConnect	Conn, "SQLITE;;:memory:","",""
.
	OPEN	ORDERS_DISK,"OrdersDt.txt",VIEW="OrdersDt"
	READ	ORDERS_DISK,KEY1;;
	LOOP
	FILEPI	99;ORDERS_DISK
	READKS	ORDERS_DISK;ORDERID=ORDERID,ORDERDATE=ORDERDT
	UNTIL	OVER
	DBSEND	Conn;"Select date('",ORDERDT,"','+10 day')"
	DBEXECUTE	Conn
	DBFETCH	Conn,F1;ORDERDT
	UPDATE	ORDERS_DISK;ORDERDATE=ORDERDT
	FILEPI	0
	REPEAT
	FILEPI	0
	CLOSE	ORDERS_DISK

See Also: SQLite, SCHEMA, FILTER, Disk I/O Instructions