DateTime now nanos

Hi all.
In Playground I write these lines:

Transcript cr; show: DateAndTime now printString.
Transcript cr; show: (DateAndTime fromSeconds: (DateAndTime now asSeconds))
printString.

and in the Transcript window I get these results:

2020-06-15T14:33:06.630367+10:00
2020-06-15T14:33:06+10:00

I was expecting these two to be the same.

I was hoping to use DateAndTime fromSeconds: and aDateAndTime asSeconds to
convert a DateAndTime to/from a number in order to store it in SQLite, but I
don't like the way the nanos are dropped using this method.
It's all good if I remember to call DateAndTime now truncated, and drop the
nanos myself, but that might be all over the place in my application.

Instead, is there a better way to convert a DateAndTime to and from a
number?  



--
Sent from: http://forum.world.st/Pharo-Smalltalk-Users-f1310670.html

Jeff,

I can't comment on the problem per se, as I am not a frequent Pharo user.

But whenever something would be all over the place, I'd suggest thinking
about wrapping the problem into its own class, so that your point in
time is not an instance of DateAndTime but one of your wrapper class.
This wrapper could be a subclass (I wouldn't recommend that) or some
kind of facade for a DateAndTime specialized for your needs. You can
then implement the droppoing of Nanos and whatever else may be needed
for your SQLite handling in this specialized class and therefor avoid
forgetting to do this on one of the many places in your application.

Just an idea to get a handle on your problem, maybe not worth much more
than a few cents.

Joachim


Am 15.06.20 um 06:44 schrieb Jeff Gray:
--
-----------------------------------------------------------------------
Objektfabrik Joachim Tuchel          mailto:[hidden email]
Fliederweg 1                         http://www.objektfabrik.de
D-71640 Ludwigsburg                  http://joachimtuchel.wordpress.com
Telefon: +49 7141 56 10 86 0         Fax: +49 7141 56 10 86 1

Isn't time represented as numbers like floats; it is meaningless to test for equality. It's only meaningful to test for equality within a tolerance. This gives the, possibly unexpected, π != π (pi != pi).
The reason is that the first pi could be 3.14 while the second could be 3.14159265358979 ---- and the second is, of course, far from accurate.

Hi all.
In Playground I write these lines:

Transcript cr; show: DateAndTime now printString.
Transcript cr; show: (DateAndTime fromSeconds: (DateAndTime now asSeconds))
printString.

and in the Transcript window I get these results:

2020-06-15T14:33:06.630367+10:00
2020-06-15T14:33:06+10:00

I was expecting these two to be the same.

I was hoping to use DateAndTime fromSeconds: and aDateAndTime asSeconds to
convert a DateAndTime to/from a number in order to store it in SQLite, but I
don't like the way the nanos are dropped using this method.
It's all good if I remember to call DateAndTime now truncated, and drop the
nanos myself, but that might be all over the place in my application.

Instead, is there a better way to convert a DateAndTime to and from a
number?  



--
Sent from: http://forum.world.st/Pharo-Smalltalk-Users-f1310670.html

I know what you mean although with something as ubiquitous as a date, which
may be initiated from a ui control, or initialised to now, or some other
date, like now, or midnight etc, I wouldn't expect to wrap that up in
anything.
It's hard to say as I'm just working on a persistence layer right now, so
maybe I shouldn't worry about it until I have some concrete examples that
cause issues.  



--
Sent from: http://forum.world.st/Pharo-Smalltalk-Users-f1310670.html

I expect you are right.
I generally won't be looking for an exact match, even down to time. Maybe a
date match, but yes more often it would be greater than less than etc.

Maybe I should just be asking what people normally do to store dates in
SQLite. (I'm guessing there aren't that many...)



--
Sent from: http://forum.world.st/Pharo-Smalltalk-Users-f1310670.html

Hi jeff

Thanks Steph.

Running a similar experiment using ZTimestamp:

|ts1 ts2|
ts1 := ZTimestamp now.
tsNumber := ts1 asUnixTime.
ts2 := ZTimestamp fromUnixTime: tsNumber.
Transcript cr; show: ts1.
Transcript cr; show: ts2.

I get the output:
2020-06-15T07:05:59Z
2020-06-15T07:05:59Z




--
Sent from: http://forum.world.st/Pharo-Smalltalk-Users-f1310670.html

Hi Jeff,

That is because ZTimestamp, by design, has second precision.

To come back to your original question, it is not so simple to represent a timestamp as a single number. This is typically done by computing the distance to a so called epoch (reference). And you have to take the desired precision into account.

You could do something like

| epoch now integer timestamp |
epoch := DateAndTime year: 2000 month: 1 day: 1 hour: 0 minute: 0 second: 0 nanoSecond: 0 offset: Duration zero.
now := DateAndTime now.
integer := (now - epoch) asNanoSeconds.
timestamp := epoch + integer nanoSeconds.

This works, but loses the timezone offset.

That is why most databases use a string representation.
For example using the tool that Stéphane described.

For example, P3, the PostgreSQL client uses the ISO 8601 format YYYY-MM-DDThh:mm:ss.s+ZZ:zz

HTH,

Sven

Wow thanks all. Interesting topic.
I think the short answer I can glean here is use a string.
I wasn't sure as the SQLite doco suggested either string or number and
number felt the right thing to choose.
Clearly not :-)

 



--
Sent from: http://forum.world.st/Pharo-Smalltalk-Users-f1310670.html

On Mon, Jun 15, 2020 at 01:23:06AM -0500, Jeff Gray wrote:
> Maybe I should just be asking what people normally do to store dates in
> SQLite. (I'm guessing there aren't that many...)

The Pharo SQLite binding stores DateAndTime instances as strings. When
fetching from the database, if the declared column type looks
date/time-ish then that string is turned back into a DateAndTime
instance.

See this blog post:

  https://www.samadhiweb.com/blog/2015.04.09.nbsqlite3.datetime.html

The blog post refers to NBSQLite3Connection, which is for the Pharo 4
NativeBoost FFI version.

Current version is here:

  https://github.com/pharo-rdbsm/Pharo-SQLite3

I've entertained the idea of making it pluggable, so that the
application programmer gets to decide whether to store/fetch Unix epoch
integers, Julian day floats, etc. No action, daydreaming only.

Pierce

Thanks Pierce. I thought I had to transform them myself. Need to play more...



--
Sent from: http://forum.world.st/Pharo-Smalltalk-Users-f1310670.html

I may be misunderstanding, but is the example in the link declaring a sqlite
column of type datetime? I didn't think you could do that.

I just created a person table with four text columns and wrote this in my
playground:

|conn sql bindings|
conn := SQLite3Connection on: 'C:\Users\JeffGray\test.db'.
conn open.
sql := 'insert into person (active, created, first_name, last_name) values
("Y", ?, "Jeff", "Gray")'.
bindings := OrderedCollection new.
bindings add: DateAndTime now.
conn execute: sql with: bindings.
conn close.

Then I wrote this to pull it out:
|conn sql c rows|
conn := SQLite3Connection on: 'C:\Users\JeffGray\test.db'.
conn open.
sql := 'select created from person where rowid = 1'.
c := conn execute: sql.
row := c rows first.
(row at: 'created') inspect.
conn close.

The object I get is a byte string. Is there any magic I can do to get a
DateAndTime or:
1. do I have to know the column is a date and call the DateAndTime
fromString:; or
2. can you make a sqlite column know it's a date (like in the example link);
or
3. is there something else I'm missing?

 



--
Sent from: http://forum.world.st/Pharo-Smalltalk-Users-f1310670.html

On Wed, Jun 17, 2020 at 02:24:20AM -0500, Jeff Gray wrote:
> I may be misunderstanding, but is the example in the link declaring a sqlite
> column of type datetime? I didn't think you could do that.

SQLite's typing is different from most other SQL engines. See
https://sqlite3.org/datatype3.html. According to that document datetime
is a valid column type. Indeed AFAICT any column type is allowed as long
as it conforms to SQLite's naming requirements.

When writing an instance of DateAndTime, Pharo-SQLite stores it as a
string in the database. When reading, if the database indicates that the
actual type of the value fetched is a string, Pharo-SQLite checks the
column type declaration and turns the string back into a DateAndTime
instance if the declared SQL column type is one of SQLite3Library
dateTimeTypes.

I added table creation SQL with exactly one declared type of datetime to
your example (slightly rewritten):

  | conn sql bindings |
  conn := SQLite3Connection openOn: '/tmp/dt.db'.
  [  conn execute: 'drop table person'.
     conn execute: 'create table person (active, created datetime, first_name, last_name)'.
     sql := 'insert into person (active, created, first_name, last_name) values ("Y", ?, "Jeff", "Gray")'.
     conn execute: sql value: DateAndTime now.
  ] ensure: [ conn close ].

Reading it back gives a DateAndTime instance.

Previous mail I mentioned pluggablity. It turns out when one can declare
any column type one has pluggability already. Below example declares the
column 'created' as type 'unixtime' and writes the value 'DateAndTime
now asUnixTime'.

  | conn sql bindings |
  conn := SQLite3Connection openOn: '/tmp/dt.db'.
  [  conn execute: 'drop table person'.
     conn execute: 'create table person (active, created unixtime, first_name, last_name)'.
     sql := 'insert into person (active, created, first_name, last_name) values ("Y", ?, "Jeff", "Gray")'.
     conn execute: sql value: DateAndTime now asUnixTime.
  ] ensure: [ conn close ].

Reading it backs gives an integer. The application programmer, knowing
the column type declaration is unixtime, can then turn that into a
DateAndTime instance.

Pierce