DateTime now nanos

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
14 messages Options
Reply | Threaded
Open this post in threaded view
|

DateTime now nanos

Jeff Gray
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

Reply | Threaded
Open this post in threaded view
|

Re: DateTime now nanos

jtuchel
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:

> 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
>
>

--
-----------------------------------------------------------------------
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



Reply | Threaded
Open this post in threaded view
|

Re: DateTime now nanos

Trygve
In reply to this post by Jeff Gray
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.

On 2020.06.15 06:44, Jeff Gray wrote:
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


--

The essence of object orientation is that objects collaborate  to achieve a goal.
Trygve Reenskaug      
[hidden email]
Morgedalsvn. 5A       
http://folk.uio.no/trygver/
N-0378 Oslo             
http://fullOO.info
Norway                     Tel: (+47) 468 58 625

Reply | Threaded
Open this post in threaded view
|

Re: DateTime now nanos

Jeff Gray
In reply to this post by jtuchel
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

Reply | Threaded
Open this post in threaded view
|

Re: DateTime now nanos

Jeff Gray
In reply to this post by Trygve
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

Reply | Threaded
Open this post in threaded view
|

Re: DateTime now nanos

Stéphane Ducasse
In reply to this post by Jeff Gray
Hi jeff

I do not have the exact answer and too busy now. 
My impression that is printString is not what we want. 
I want a 
( DatePrinter on: DateAndTime now) 
printIso;
noMicrosecond;
noTimezone;
print

Because your printString will certainly not work with my needs?
Now I got no time to work on this but this would be a great project.
So instead of bloating dateAndTime and friends I would separate it. 

Did you have a look at the ZTimeStamp project of Sven? Because I remember
he got some facilities but my memory is to vage. 

S.

On 15 Jun 2020, at 06:44, Jeff Gray <[hidden email]> wrote:

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


--------------------------------------------
Stéphane Ducasse
03 59 35 87 52
Assistant: Aurore Dalle 
FAX 03 59 57 78 50
TEL 03 59 35 86 16
S. Ducasse - Inria
40, avenue Halley, 
Parc Scientifique de la Haute Borne, Bât.A, Park Plaza
Villeneuve d'Ascq 59650
France

Reply | Threaded
Open this post in threaded view
|

Re: DateTime now nanos

Jeff Gray
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

Reply | Threaded
Open this post in threaded view
|

Re: DateTime now nanos

Sven Van Caekenberghe-2
Hi Jeff,

> On 15 Jun 2020, at 09:08, Jeff Gray <[hidden email]> wrote:
>
> 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

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


Reply | Threaded
Open this post in threaded view
|

Re: DateTime now nanos

Richard O'Keefe
In reply to this post by Trygve
Comparing floating-point numbers is perfectly well defined.
Floating point NUMBERS are precise.
Floating point OPERATIONS are approximate.
(See for example the LIA-1 standard.)

In the same way, comparing timestamps *from the same source*
is perfectly well defined.
Considering that light travels 30 cm (1 foot) in 1 nanosecond,
events occurring inside a single laptop are far enough apart
that recording times to nanosecond precision is questionable.
The problem is not Pharo, it's Physics.

Considering also operating system jitter, I decided that
millisecond resolution was good enough for my own library.

Now to another issue.
In Pharo 9, a DateAndTime contains
 - a julianDayNumber
 - a count of seconds
 - a count of nanos(econds)
 - a time zone offset (which is a Duration).
Converting everything *except* the zone offset to a number
is trivial, using #asNanoSeconds (which breaks the Smalltalk
naming rules; nanoseconds, being a single word, should not have
an internal capital).  Sadly, there is no corresponding
DateAndTIme fromNanoSeconds: ns [offset: aDuration]
but it is easy enough to clone #fromSeconds:[offset:] and adapt
them.  The reason I'm NOT going to do that is that if you do
not preserve the offset, you are STILL losing information.

What you need to do is to use a string, not a number:
  stringForDB := aDateAndTime printString.
  aDateAndTime := DateAndTime readFrom: stringFromDB.




On Mon, 15 Jun 2020 at 18:07, Trygve Reenskaug <[hidden email]> wrote:
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.

On 2020.06.15 06:44, Jeff Gray wrote:
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


--

The essence of object orientation is that objects collaborate  to achieve a goal.
Trygve Reenskaug      
[hidden email]
Morgedalsvn. 5A       
http://folk.uio.no/trygver/
N-0378 Oslo             
http://fullOO.info
Norway                     Tel: (+47) 468 58 625

Reply | Threaded
Open this post in threaded view
|

Re: DateTime now nanos

Jeff Gray
In reply to this post by Jeff Gray
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

Reply | Threaded
Open this post in threaded view
|

Re: DateTime now nanos

Pierce Ng-3
In reply to this post by Jeff Gray
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

Reply | Threaded
Open this post in threaded view
|

Re: DateTime now nanos

Jeff Gray
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

Reply | Threaded
Open this post in threaded view
|

Re: DateTime now nanos

Jeff Gray
In reply to this post by Pierce Ng-3
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

Reply | Threaded
Open this post in threaded view
|

Re: DateTime now nanos

Pierce Ng-3
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 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.

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