On 12/29/15, Cecil Westerhof <cldwesterhof at gmail.com> wrote:
> I first had the following table:
> CREATE TABLE simpleLog (
> datetime TEXT NOT NULL PRIMARY KEY DEFAULT CURRENT_TIMESTAMP,
> description TEXT NOT NULL
> )
>
> ?But datetime then takes 19 bytes. I understood you can also use an Integer
> or Real and that this should be more efficient. At the moment I have the
> following (I do not expect more as one record in a second):
> CREATE TABLE simpleLog (
> datetime INT NOT NULL PRIMARY KEY DEFAULT (strftime('%s')),
> description TEXT NOT NULL
> )
>
> And a select is then done by (in my select minute is precision enough):
> SELECT strftime('%Y-%m-%d %H:%M', datetime, 'unixepoch', 'localtime') as
> datetime
> , description
> FROM simpleLog
> ORDER BY datetime DESC
>
> Is this a good way to go, or is there a better way?
What you have should work well.
If you store the date/times as a floating-point Julian Day Number, you
can omit the 'unixepoch' on query. Use julianday('now') instead of
strftime('%s','now') on the DEFAULT. That seems a little simpler to
me, and you get millisecond resolution on the date/times instead of
just second resolution. But the unix-time format is more familar to
many programmers, and can be stored in 4 bytes instead of 8.
--
D. Richard Hipp
drh at sqlite.org
