« Previous  |  Next »

NBSQLite3 Date Time Handling

9 April 2015

SQLite does not support date/time as a built-in data type. Instead, SQLite provides date and time functions to deal with ISO 8601-format strings, floating point values representing Julian days, and Unix epoch time integers.

I've added date/time handling to NBSQLite3, so that storing a DateAndTime object results in an ISO 8601 string in the database, and reading such a string from the database, provided the column type is declared as DateTime, yields a DateAndTime object.

Here's an example using the low-level API:

| dt db stmt |

dt := DateAndTime year: 2015 month: 4 day: 1.

db := NBSQLite3BaseConnection openOn: ':memory:'.
[
    db basicExecute: 'create table x (xv datetime)'.
    db basicExecute: 'begin'.
    stmt := db prepare: 'insert into x values (?)'.
    stmt at: 1 putDateTime: dt.
    stmt step. stmt finalize.
    db basicExecute: 'commit'.

    stmt := db prepare: 'select xv from x'.
    stmt basicExecute: [ :row |
        | v |
        v := row dateTimeAt: 0.
        Transcript show: v year asString; cr.
        Transcript show: v month asString; cr.
        Transcript show: v dayOfMonth asString; cr ].
    stmt finalize.
] ensure: [ db close ]

And here's the example using the high-level API:

| dt db rs v |

dt := DateAndTime year: 2015 month: 4 day: 1.

db := NBSQLite3Connection openOn: ':memory:'.
[
    db basicExecute: 'create table x (xv datetime)'.
    db basicExecute: 'begin'.
    db execute: 'insert into x values (?)' with: (Array with: dt).
    db basicExecute: 'commit'.

    rs := db execute: 'select xv from x'.
    v := (rs next) at: 'xv'.
    Transcript show: v class asString; cr.
    Transcript show: v year asString; cr.
    Transcript show: v month asString; cr.
    Transcript show: v dayOfMonth asString; cr.
    rs close
] ensure: [ db close ]


<