How to Handle Date, Time and Timestamp Data Types with SQLite in Cincom® VisualWorks®
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.
In this article, we’ll demonstrate two ways to process date, time and timestamp data. One way is to take advantage of Glorp’s conversion methods, and the other way is to use SQLite’s built-in functions to achieve the same goal.
Let’s start with Glorp’s 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:
“========== 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.
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 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 Timestamp 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.
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.