SQLite storage timestamp precision

[Lee Henson]

Hi

I'm writing an app that needs to be able to inspect it's own event
stream history, and run some rudimentary queries over it. One of the
interesting fields to me is the Timestamp recorded against an event.
At the moment, in both the MsSQL and SQLite storage mechanisms, it is
stored using the database's built it "now" function, either getdate()
or datetime('now'). This obviously does not match the timestamp
recorded on the serialized event object stored in that row. I need
those two numbers to match up, down to tick-level precision. I've
amended the SQLite storage extension in commit:

http://github.com/leemhenson/ncqrs/commit/9cb98ad375d630f7d53b72adbf8da4490fccaf53

If there isn't a specific reason for recording the timestamp an event
was persisted as opposed to the event being created, it is probably
worth making this change to the other storage mechanisms.

[Pieter Joost van de Sande]

I don't see any reason for recording the timestamp of persistence as opposed to event creation. I'll update the other event stores as well and pull your fix tonight.

[Chris Chilvers]

Shouldn't ticks be stored as a bigint? Though I'd personally prefer to
store the timestamp in a proper date/time field rather than as an int.

On Jul 14, 12:40 pm, Pieter Joost van de Sande <p...@born2code.net>
wrote:

> I don't see any reason for recording the timestamp of persistence as opposed
> to event creation. I'll update the other event stores as well and pull your
> fix tonight.
>
>
>
> On Wed, Jul 14, 2010 at 12:10 PM, Lee Henson <lee.m.hen...@gmail.com> wrote:
> > Hi
>
> > I'm writing an app that needs to be able to inspect it's own event
> > stream history, and run some rudimentary queries over it. One of the
> > interesting fields to me is the Timestamp recorded against an event.
> > At the moment, in both the MsSQL and SQLite storage mechanisms, it is
> > stored using the database's built it "now" function, either getdate()
> > or datetime('now'). This obviously does not match the timestamp
> > recorded on the serialized event object stored in that row. I need
> > those two numbers to match up, down to tick-level precision. I've
> > amended the SQLite storage extension in commit:
>

> >http://github.com/leemhenson/ncqrs/commit/9cb98ad375d630f7d53b72adbf8...

[Lee Henson]

> Shouldn't ticks be stored as a bigint?

I was under the impression that INT, BIG INT et al were all just
stored in the same way by sqlite. This paragraph in the docs is
somewhat ambiguous though:

"Note that a storage class is slightly more general than a datatype.
The INTEGER storage class, for example, includes 6 different integer
datatypes of different lengths. This makes a difference on disk. But
as soon as INTEGER values are read off of disk and into memory for
processing, they are converted to the most general datatype (8-byte
signed integer). And so for the most part, "storage class" is
indistinguishable from "datatype" and the two terms can be used
interchangably."
(http://www.sqlite.org/datatype3.html)

I have stored a tick value in that INTEGER column and read it back
successfully so it must be capable of storing longs like that.

> Though I'd personally prefer to
> store the timestamp in a proper date/time field rather than as an int.

Again, from the docs:

"SQLite does not have a storage class set aside for storing dates and/
or times. Instead, the built-in Date And Time Functions of SQLite are
capable of storing dates and times as TEXT, REAL, or INTEGER values:

TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").
REAL as Julian day numbers, the number of days since noon in Greenwich
on November 24, 4714 B.C. according to the proleptic Gregorian
calendar.
INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00
UTC.

Applications can chose to store dates and times in any of these
formats and freely convert between formats using the built-in date and
time functions."

I read that as saying a datetime column in sqlite is incapable of
storing tick-precision datetimes. It looks like the most accurate you
can be is millisecond.

[pjvds]

Today I wanted to update the MsSql based event store, but it seems
that Chris already updated this when adding serialization support.