Two Ways to Handle Date, Time and Timestamp Data Types with SQLite in Cincom VisualWorks

Learning two ways to process date, time and timestamp data with SQLite. 

What is SQLite?  

SQLite is a serverless, C-language SQL database engine that is fast, highly reliable, and self-contained. The popularity of this database engine is due to its serverless nature, which means it operates without the need for a separate server. 

According to the SQLite website: 

• SQLite is one of the world’s most popular database engines. 

• SQLite is built into all mobile phones and most computers. 

• SQLite is bundled with a plethora of other applications that people use on a daily basis. 

• SQLite is a C-language library that implements a small, but fast, self-contained and highly reliable, full-featured SQL database engine. 

• SQLite file format is stable, cross-platform, and backwards compatible (it’s promised to be kept that way until 2050 by its creators). 

• SQLite database files are frequently used as containers to transfer rich content between systems and as a long-term data archival format. 

• SQLite databases are in use in excess of one trillion (1e12). 

• SQLite source code is in the public domain and can be used by anyone for any purpose. 

SQLite does not support built-in date, time and timestamp storage classes. Users normally make use of its built-in date and time functions to handle these data types. To show how to do this, we’ll demonstrate two ways to process date, time and timestamp data: 

1. Take advantage of Glorp’s conversion methods. 

1. Use SQLite’s built-in functions to achieve the same goal. 

Method 1: Using Glorp’s Conversion Methods 

Let’s start with Glorp’s (Generic Lightweight Object-Relational Persistence) way. But before doing that, please note that in addition to loading SQLite3EXDI into your image, you need to load StoreForSQLite3 as well. 

See the following code example:

See the following code example:
"========== INSERT  DATA ========="
"Connect to a SQLite database."
conn := SQLite3Connection new
			environment: 'aSQLiteDatabase';
			username: '';
			password: ''.
conn connect.

sess := conn getSession.

"Drop the table if existed."
[sess prepare: 'DROP TABLE testtime'.
sess execute.
sess answer; answer] on: Error
					do: [:ex|"Ignore errors."].

"Create a test table."
sess prepare: 'CREATE TABLE testtime( time TEXT, date TEXT, timestamp TEXT)'.
sess execute.
sess answer; answer.

"Prepare the Time, Date and Timestamp data for insertion."
aTime := Time now.
aDate := Date today.
aTimestamp := Timestamp now.

plat := DatabasePlatform new.
strm := String new writeStream.

plat printTime: aTime isoFormatOn: strm milliseconds: true.
timeString := strm contents.
strm reset.

plat printDate: aDate isoFormatOn: strm.
dateString := strm contents.
strm reset.

plat printDate: aTimestamp isoFormatOn: strm.
strm space.
plat printTime: aTimestamp isoFormatOn: strm milliseconds: true.
timestampString := strm contents.

"Insert the converted data."
sess prepare: 'INSERT INTO testtime VALUES( ?, ?, ? )'.
sess bindInput: (Array with: timeString with: dateString with: timestampString).
sess execute.
sess answer; answer.

"Disconnect from the database."
conn disconnect.

"================== SELECT  DATA =================="

"Connect to a SQLite database."
conn := SQLite3Connection new
			environment: 'aSQLiteDatabase';
			username: '';
			password: ''.
conn connect.

sess := conn getSession.

"Select the data from the test table to verify correctness."
sess prepare: 'SELECT * FROM testtime'.
sess execute.
[(ans := sess answer) = #noMoreAnswers]
	whileFalse: [ans = #noAnswerStream
					ifFalse: [results := ans upToEnd]].

"Convert the retrieved strings to Time, Date and Timestamp objects."
plat := DatabasePlatform new.
row := results first.
aTime := plat readTime: (row at: 1) for: nil.
aDate := plat readDate: (row at: 2) for: nil.
aTimestamp := plat readTimestamp: (row at: 3) for: nil.

"Print the retrieved data to Transcript."
Transcript
		show: aTime printString; cr;
		show: aDate printString; cr;
		show: aTimestamp printString; cr.

"Disconnect from the database."
conn disconnect.

Method 2: Using SQLite’s Built-in Functions 

Another way is to use SQLite’s built-in functions.  Since SQLite allows you to store dates, times and timestamps as TEXT, REAL and INTEGER values, we’ll use three code examples to demonstrate the usage.  However, since normally you need to convert Smalltalk date, time and timestamp objects to strings before inserting them into SQLite tables and converting the strings retrieved to Smalltalk date, time and timestamp objects, you still need to load the StoreForSQLite3 package. 

First, the following is an example for storing SQLite Timestamp as TEXT: 

"Connect to a SQLite database."
conn := SQLite3Connection new
			environment: 'aSQLiteDatabase';
			username: '';
			password: ''.
conn connect.

sess := conn getSession.

"Drop the table if existed."
[sess prepare: 'DROP TABLE testtime'.
sess execute.
sess answer; answer] on: Error
					do: [:ex|"Ignore errors."].

"Create a test table."
sess prepare: 'CREATE TABLE testtime( timestamp TEXT)'.
sess execute.
sess answer; answer.

"Insert data using a built-in function."
sess prepare: 'INSERT INTO testtime VALUES(datetime(''now''))'.
sess execute.
sess answer; answer.

"Select the data from the test table to verify correctness."
sess prepare: 'SELECT * FROM testtime'.
sess execute.
[(ans := sess answer) = #noMoreAnswers]
	whileFalse: [ans = #noAnswerStream
					ifFalse: [results := ans upToEnd]].

"Convert the retrieved strings to a Timestamp object."
plat := DatabasePlatform new.
row := results first.
aTimestamp := plat readTimestamp: (row at: 1) for: nil.

"Print the retrieved data to Transcript."
Transcript
		show: aTimestamp printString; cr.

"Disconnect from the database."
conn disconnect.

The second example is to store Timestamp as REAL:

"Connect to a SQLite database."
conn := SQLite3Connection new
			environment: 'aSQLiteDatabase';
			username: '';
			password: ''.
conn connect.

"Drop the table if existed."
sess := conn getSession.

"Drop the table if existed."
[sess prepare: 'DROP TABLE test_real'.
sess execute.
sess answer; answer] on: Error
					do: [:ex|"Ignore errors."].

"Create a test table."
sess prepare: 'CREATE TABLE test_real( c real)'.
sess execute.
sess answer; answer.

"Insert data using a built-in function."
sess prepare: 'INSERT INTO test_real VALUES(julianday(''now''))'.
sess execute.
sess answer; answer.

"Select the data from the test table to verify correctness."
sess prepare: 'SELECT  time(c), date(c)  FROM test_real'.
sess execute.
[(ans := sess answer) = #noMoreAnswers]
	whileFalse: [ans = #noAnswerStream
					ifFalse: [results := ans upToEnd]].

"Convert the retrieved strings to a Time and a Date object."
plat := DatabasePlatform new.
row := results first.
aTime := plat readTime: (row at: 1) for: nil.
aDate := plat readDate: (row at: 2) for: nil.

"Print the retrieved data to Transcript."
Transcript
		show: aTime printString; cr;
		show: aDate printString; cr.

"Disconnect from the database."
conn disconnect.

The last example is to store SQLite Timestamp type as INTEGER: 

"Connect to a SQLite database."
conn := SQLite3Connection new
			environment: 'aSQLiteDatabase';
			username: '';
			password: ''.
conn connect.

sess := conn getSession.

"Drop the table if existed."
[sess prepare: 'DROP TABLE test_integer'.
sess execute.
sess answer; answer] on: Error
					do: [:ex|"Ignore errors."].

"Create a test table."
sess prepare: 'CREATE TABLE test_integer( c int)'.
sess execute.
sess answer; answer.


"Insert data using a built-in function."
sess prepare: 'INSERT INTO test_integer VALUES(strftime(''%s'',''now''))'.
sess execute.
sess answer; answer.

"Select the data from the test table to verify correctness."
sess prepare: 'SELECT datetime(c, ''unixepoch'') FROM test_integer'.
sess execute.
[(ans := sess answer) = #noMoreAnswers]
	whileFalse: [ans = #noAnswerStream
					ifFalse: [results := ans upToEnd]].


"Convert the retrieved string to a Timestamp object."
plat := DatabasePlatform new.
row := results first.
aTimestamp := plat readTimestamp: (row at: 1) for: nil.

"Print the retrieved data to Transcript."
Transcript
		show: aTimestamp printString; cr.

"Disconnect from the database."
conn disconnect.

Best Practices 

Please remember that in SQLite, any column can store any type of data. For example, it allows you to insert TEXT values into REAL and INTEGER fields.  So, in order to avoid conversion failure and other problems, the best practice is to store the same type of data in a single column.