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:
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:jtuchel@objektfabrik.de 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
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
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 collaborateto achieve a goal. / Trygve Reenskaug mailto: trygver@ifi.uio.no <mailto:%20trygver@ifi.uio.no> Morgedalsvn. 5A http://folk.uio.no/trygver/ N-0378 Oslo http://fullOO.info Norway                     Tel: (+47) 468 58 625
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
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 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
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 <trygver@ifi.uio.no> 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 mailto: trygver@ifi.uio.no <%20trygver@ifi.uio.no> Morgedalsvn. 5A http://folk.uio.no/trygver/ N-0378 Oslo http://fullOO.info Norway Tel: (+47) 468 58 625
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 <jeff@rogerthedog.com> 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 http://stephane.ducasse.free.fr / http://www.pharo.org 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
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,
On 15 Jun 2020, at 09:08, Jeff Gray <jeff@rogerthedog.com> 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
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
participants (7)
-
Jeff Gray -
jtuchel＠objektfabrik.de -
Pierce Ng -
Richard O'Keefe -
Stéphane Ducasse -
Sven Van Caekenberghe -
Trygve Reenskaug