Re: [sqlite] ISO time leap second.

[Nico Williams]

On Tue, Jul 29, 2014 at 10:02 AM, Simon Slavin  wrote:
> You're never going to get non-scientific programmers to do this properly 
> anyway.  Every financial programmer knows that there are exactly 60*60*24 = 
> 86,400 seconds in a day.  You've never going to get them to use library 
> routines to work out how many seconds there are in a 30 day period.

I'm not sure that this doesn't matter to people dealing with financial
data, but I suspect that if someone cares about TAI in financial data
then they probably care about having very high resolution timestamps
too (think of high speed trading).

Perhaps the same goes for astronomers, actually: millisecond
resolution probably isn't enough if you really care about one second.
(Of course, the accumulated error from ignoring leap seconds across
decades-long intervals will be larger than one second, but still, I
think it's fair to infer that astronomers probably care about higher
time resolutions than a millisecond.

I think that's getting close to beating this poor horse a bit too
much.  Count me as in favor ignoring second values of 60 when parsing
seconds.

Dealing with TAI reliably would require extending the existing date
functions, or adding new ones, to at minimum do TAI<->UTC (and/or
Julan day) conversions.  Given that it should be possible to produce
functions that do date arithmetic by converting inputs to TAI as
necessary (assuming it's possible to tell UTC inputs from TAI inputs).

FYI: http://cr.yp.to/proto/utctai.html

DJB also has a library for converting between TAI and UTC that might
be useful here: http://cr.yp.to/libtai.html .

Nico
--
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ISO time leap second.

[Simon Slavin]

On 29 Jul 2014, at 5:36am, Nico Williams  wrote:

> Note that SQLite3 apparently does no
> corrections for leap seconds anyways in date arithmetic (which it
> can't do if you're using numeric arithmetic on Julian days anyways!),
> which it would have to do (since UTC is non-uniform).  I'm not
> entirely sure what it means for anyone who really cares about TAI, or
> who they might be besides astronomers.  If all you want to do is parse
> dates, then indeed, raising an error would be better than ignoring a
> leap second, but if you want to do any arithmetic on dates, then
> you're already in trouble, given which I think the fair thing to do is
> ignore leap seconds, but I'll freely admit that it's not ideal.

Agree with all of that.

You're never going to get non-scientific programmers to do this properly 
anyway.  Every financial programmer knows that there are exactly 60*60*24 = 
86,400 seconds in a day.  You've never going to get them to use library 
routines to work out how many seconds there are in a 30 day period.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ISO time leap second.

[Nico Williams]

On Mon, Jul 28, 2014 at 7:54 PM, James K. Lowden
 wrote:
> On Mon, 28 Jul 2014 16:32:42 -0500
> Nico Williams  wrote:
>> Raising errors is not that useful here: most users who ever run into
>> such timestamps will not really know what to do with them.
>
> I think you're quite wrong here.  Until presented with evidence to the
> contrary, I would assert the opposite: that users who run into leap
> seconds *do* know what to do with them because they need to, for
> astronomical reasons or somesuch.  And have already done so.

That is a fair point.  Note that SQLite3 apparently does no
corrections for leap seconds anyways in date arithmetic (which it
can't do if you're using numeric arithmetic on Julian days anyways!),
which it would have to do (since UTC is non-uniform).  I'm not
entirely sure what it means for anyone who really cares about TAI, or
who they might be besides astronomers.  If all you want to do is parse
dates, then indeed, raising an error would be better than ignoring a
leap second, but if you want to do any arithmetic on dates, then
you're already in trouble, given which I think the fair thing to do is
ignore leap seconds, but I'll freely admit that it's not ideal.

Nico
--
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ISO time leap second.

[James K. Lowden]

On Mon, 28 Jul 2014 16:32:42 -0500
Nico Williams  wrote:

> The word "solar" does not appear on
> http://sqlite.org/lang_datefunc.html .  Instead it's explicitly stated
> that "Universal Coordinated Time (UTC) is used".

Quite.  

> Still, ISTM that "UTC is used" implies handling of leap seconds, and
> the simplest way to handle them is to parse them but alias them to a
> neighboring second.  

That wouldn't be a step forward.  If you want to handle leap seconds,
you can't do it by igoring them.  If you simply accept 60 seconds to
mean the next minute, you won't have helped the leap-second crowd at
all, and you *will* have permitted erroneous input in many, many
cases.  

ISTM we're speculating here.  In 10 years, has anyone posted a message
to this list explaining why his leap-seconds can't be represented in
SQLite?  I mean, there have been 3 such events in that time, surely
enough to trip someone up.  (http://maia.usno.navy.mil/ser7/tai-utc.dat)

For those following along at home, iIf you don't think the relationship
between UTC and the readout you're apt to see in your system logs is
ambiguous at best, see http://www.eecis.udel.edu/~mills/leap.html.  

> Raising errors is not that useful here: most users who ever run into
> such timestamps will not really know what to do with them.  

I think you're quite wrong here.  Until presented with evidence to the
contrary, I would assert the opposite: that users who run into leap
seconds *do* know what to do with them because they need to, for
astronomical reasons or somesuch.  And have already done so.  

--jkl
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ISO time leap second.

[jose isaias cabrera]

"Igor Tandetnik" wrote...


On 7/28/2014 12:37 PM, jose isaias cabrera wrote:

I claim that I am not an expert, but is this one a valid ISO time
stamp?  If so, then that ISO must be revised, as that time does not
really exists ever.


That time does really exist, occasionally: 
http://en.wikipedia.org/wiki/Leap_second


Every day, I want to be more and more like Igor. :-)  I thought I had you on 
this one... ;-)


josé 


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ISO time leap second.

[Nico Williams]

On Mon, Jul 28, 2014 at 12:20 PM, Keith Medcalf  wrote:
> It may or may not be a valid timestamp depending on what your time 
> representation is.  SQLite does not use UTC (which is an artificial timescale 
> maintained by a bunch of atomic clocks).  SQLite (and most other things that 
> keep time, other than artificial atomic clocks) use Mean Solar Time.

The word "solar" does not appear on
http://sqlite.org/lang_datefunc.html .  Instead it's explicitly stated
that "Universal Coordinated Time (UTC) is used".

ISO-8601 is mentioned in passing, and it's not clear if SQLite3 is
intended to parse all ISO-8601 timestamps or if ISO-8601 is merely
inspiration.

Still, ISTM that "UTC is used" implies handling of leap seconds, and
the simplest way to handle them is to parse them but alias them to a
neighboring second.  (Alternatively SQLite3 could have support TAI or
some other time standard that doesn't have leap seconds, and then
support conversions to/from UTC, but that wouldn't be easy to use.)

FTR, I'm with Jan N.: timestamps with 60 seconds should be parsed,
with the extra second "ignored", for some value of "ignored".  There's
two possible values of "ignored" here: s/60/59/, or s/60/59/ then +
1s.  I'm not sure which of those is best.

> [...]
> Because UTC is a discontiguous scale which "steps" occasionally to keep the 
> "current" UTC time in sync with "real time" (as in Mean Solar Time), there is 
> no way to convert between the two without having a massive table of the 
> discontiguities.  [...]

The table would hardly be massive (it'd be inaccurate for times before
any leap seconds were computed, and it'd be massive as thousands of
years pass from now, but who cares).  But that's neither here nor
there.

We just don't need to such a table in SQLite3; it won't matter whether
it is massive.  We need only be able to *parse* timestamps with extra
seconds, because parsing them is better than not.  Raising errors is
not that useful here: most users who ever run into such timestamps
will not really know what to do with them.  Yes, the extra seconds
need to be handled in some way, and aliasing them to nearby times is
plenty good enough.

Nico
--
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ISO time leap second.

[Keith Medcalf]

It may or may not be a valid timestamp depending on what your time 
representation is.  SQLite does not use UTC (which is an artificial timescale 
maintained by a bunch of atomic clocks).  SQLite (and most other things that 
keep time, other than artificial atomic clocks) use Mean Solar Time.

The rules for Solar Time are that a day contains 24 hours, an hour contains 60 
minutes, and a minute contains 60 seconds.  Period.  There are no exceptions.  
The "length" of a second is variable.  Were this not the case then one would 
not be able to know that the time represented by 1735-03-21 12:00 corresponded 
to the time when the sun was directly overhead on spring equinox of the year 
1735.

While the UTC (artificial atomic timescale) permits a minute to contain 59, 60, 
or 61 seconds in order to allow UTC to approximate (be corrected to) GMT, UTC 
is not GMT and is not based on Solar Time.  It is a discontiguous timescale for 
machines based on the fluctuation of energy states at the quantum level in 
specific radioactive isotopes.  It is not based on the revolution of the earth 
around its axis or of the earth around the sun, as is Mean Solar Time.

Because UTC is a discontiguous scale which "steps" occasionally to keep the 
"current" UTC time in sync with "real time" (as in Mean Solar Time), there is 
no way to convert between the two without having a massive table of the 
discontiguities.  All time calculations for days, months, years, day of week, 
leap years, and on and on are based on Solar Time, which is based on the 
rotation of the earth, and not the vibration of electrons between energy states 
in radioactive isotopes.

So a "year" represents one rotation of the earth around the sun, and a "day" 
represents one rotation of the earth around its axis (less precession error, 
which is why it is Mean Solar Time, and not Apparent Solar Time).

>-Original Message-
>From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>boun...@sqlite.org] On Behalf Of jose isaias cabrera
>Sent: Monday, 28 July, 2014 10:38
>To: General Discussion of SQLite Database
>Subject: Re: [sqlite] ISO time leap second.
>
>
>"Igor Tandetnik" wote...
>
>> On 7/28/2014 11:49 AM, Jan Nijtmans wrote:
>>> 2014-07-28 17:10 GMT+02:00 Igor Tandetnik <i...@tandetnik.org>:
>>>> All your fix does is have the parser accept "60" as valid seconds
>field.
>>>> That's not very interesting.
>
>> julianday('2012-06-30T23:59:60'), and how should it compare with
>
>I claim that I am not an expert, but is this one a valid ISO time stamp?
>If
>so, then that ISO must be revised, as that time does not really exists
>ever.
>As the clock changes from the 59 second to the 60, a set of updates for
>minutes, hours, days, month and year, should happen and that 60 then
>becomes
>0.  (Thinking out-loud)
>
>josé
>
>
>___
>sqlite-users mailing list
>sqlite-users@sqlite.org
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ISO time leap second.

[Jan Nijtmans]

2014-07-28 18:28 GMT+02:00 Igor Tandetnik :
> What do you mean "ignore"? Can you specify precisely what the semantics of
> such a timestamp should be, in all the date/time manipulation operations
> SQLite supports?
Just as julianday('2012-06-30T24:00:00') is exactly equal to
julianday('2012-07-01T00:00:00'), my proposal is that
julianday('2012-06-30T23:59:60') is handled as being
equal to this exact same timestamp. That's easiest to
implement changing only 2 bytes to SQLite's source code.
OK, OK, the documentation at 
should be updated to, but that's just a single change 59 -> 60

B.T.W., try "man strftime" on Ubuntu:
>   %S The  second as a decimal number (range 00 to 60).  (The range is
>  up to 60 to allow for occasional leap seconds.)

It looks like the POSIX strftime() function is already
adapted to handle this 'problem' exactly the same way.

Regards,
   Jan Nijtmans
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ISO time leap second.

[Igor Tandetnik]

On 7/28/2014 12:37 PM, jose isaias cabrera wrote:

I claim that I am not an expert, but is this one a valid ISO time
stamp?  If so, then that ISO must be revised, as that time does not
really exists ever.


That time does really exist, occasionally: 
http://en.wikipedia.org/wiki/Leap_second

--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ISO time leap second.

[jose isaias cabrera]

"Igor Tandetnik" wote...


On 7/28/2014 11:49 AM, Jan Nijtmans wrote:

2014-07-28 17:10 GMT+02:00 Igor Tandetnik :

All your fix does is have the parser accept "60" as valid seconds field.
That's not very interesting.



julianday('2012-06-30T23:59:60'), and how should it compare with


I claim that I am not an expert, but is this one a valid ISO time stamp?  If 
so, then that ISO must be revised, as that time does not really exists ever. 
As the clock changes from the 59 second to the 60, a set of updates for 
minutes, hours, days, month and year, should happen and that 60 then becomes 
0.  (Thinking out-loud)


josé


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ISO time leap second.

[Igor Tandetnik]

On 7/28/2014 11:49 AM, Jan Nijtmans wrote:

2014-07-28 17:10 GMT+02:00 Igor Tandetnik :

All your fix does is have the parser accept "60" as valid seconds field.
That's not very interesting.


Yes, that's exactly all that I'm after. ISO 8601 does not specify how
leap seconds are handled


... whereas SQLite does need to handle them, somehow, once they are allowed.


It's perfectly OK for fossil/SQLite to ignore the leap second, it's not OK that 
an error-message is produced
for a valid ISO 8601 time-stamp.


What do you mean "ignore"? Can you specify precisely what the semantics 
of such a timestamp should be, in all the date/time manipulation 
operations SQLite supports? Say, what should be the value of 
julianday('2012-06-30T23:59:60'), and how should it compare with 
julianday('2012-06-30T23:59:59') and julianday('2012-07-01T00:00:00') ?

--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ISO time leap second.

[Jan Nijtmans]

2014-07-28 17:10 GMT+02:00 Igor Tandetnik :
> All your fix does is have the parser accept "60" as valid seconds field.
> That's not very interesting.

Yes, that's exactly all that I'm after. ISO 8601 does not specify how
leap seconds are handled, it just specified which date-stamps are
valid and which not. It's perfectly OK for fossil/SQLite to ignore
the leap second, it's not OK that an error-message is produced
for a valid ISO 8601 time-stamp. ISO specifies that seconds
must be between 0 and 60, not between 0 and 59, just as hours
can be between 0 and 24, not between 0 and 23 (which
SQLite's parser adheres to)


>ISO 8601 uses the 24-hour clock system. The basic format is [hh][mm][ss] 
> and the extended format is [hh]:[mm]:[ss].
>
>[hh] refers to a zero-padded hour between 00 and 24 (where 24 is only used 
> to denote midnight at the end of a calendar day).
>[mm] refers to a zero-padded minute between 00 and 59.
>[ss] refers to a zero-padded second between 00 and 60 (where 60 is only 
> used to denote an added leap second).

That's not what SQLite implements (although it is very close)

Regards,
  Jan Nijtmans
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ISO time leap second.

[Igor Tandetnik]

On 7/28/2014 10:58 AM, Jan Nijtmans wrote:

2014-07-28 16:53 GMT+02:00 Richard Hipp :

Better to keep the code simpler and just say that SQLite (and hence Fossil)
does not know about leap seconds.


If you can do that in two bytes  ;-)


All your fix does is have the parser accept "60" as valid seconds field. 
That's not very interesting. The interesting part is a) validating that 
it actually *is* a valid timestamp (was there a leap second on that 
particular day, hour and minute) and b) doing time arithmetic in a way 
that is aware of leap seconds (e.g. datetime(t, '+1 minute') and 
datetime(t, '+60 seconds') may have to produce different results, 
depending on the value of t).


Combine that with the fact that there's no practical way to know exactly 
when leap seconds are introduced, and you are faced with a large effort 
for very little benefit (if any at all; an argument could be made that 
leap seconds introduce more problems than they solve).

--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ISO time leap second.

[Jan Nijtmans]

2014-07-28 16:53 GMT+02:00 Richard Hipp :
> Better to keep the code simpler and just say that SQLite (and hence Fossil)
> does not know about leap seconds.

If you can do that in two bytes  ;-)

Regards,
   Jan Nijtmans
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ISO time leap second.

[Richard Hipp]

Better to keep the code simpler and just say that SQLite (and hence Fossil)
does not know about leap seconds.


On Mon, Jul 28, 2014 at 10:49 AM, Jan Nijtmans 
wrote:

> According to , the
> following should be perfectly valid:
>
> $ fossil new --date-override 2012-06-30T23:59:60 foo.fossil
> unrecognized date format (2012-06-30T23:59:60): use "-MM-DD
> HH:MM:SS.SSS"
>
> Suggested patch to fossil (and SQLite trunk, with adapted test-case):
>
> Regards,
>   Jan Nijtmans
>
> Index: src/sqlite3.c
> ==
> --- src/sqlite3.c
> +++ src/sqlite3.c
> @@ -14769,11 +14769,11 @@
>  return 1;
>}
>zDate += 5;
>if( *zDate==':' ){
>  zDate++;
> -if( getDigits(zDate, 2, 0, 59, 0, )!=1 ){
> +if( getDigits(zDate, 2, 0, 60, 0, )!=1 ){
>return 1;
>  }
>  zDate += 2;
>  if( *zDate=='.' && sqlite3Isdigit(zDate[1]) ){
>double rScale = 1.0;
>
>
> Index: src/date.c
> ==
> --- src/date.c
> +++ src/date.c
> @@ -175,11 +175,11 @@
>  return 1;
>}
>zDate += 5;
>if( *zDate==':' ){
>  zDate++;
> -if( getDigits(zDate, 2, 0, 59, 0, )!=1 ){
> +if( getDigits(zDate, 2, 0, 60, 0, )!=1 ){
>return 1;
>  }
>  zDate += 2;
>  if( *zDate=='.' && sqlite3Isdigit(zDate[1]) ){
>double rScale = 1.0;
>
> Index: test/date.test
> ==
> --- test/date.test
> +++ test/date.test
> @@ -64,13 +64,14 @@
>  datetest 1.23 julianday(12345.6) 12345.6
>  datetest 1.23b julianday('12345.6') 12345.6
>  datetest 1.24 {julianday('2001-01-01 12:00:00 bogus')} NULL
>  datetest 1.25 {julianday('2001-01-01 bogus')} NULL
>  datetest 1.26 {julianday('2001-01-01 12:60:00')} NULL
> -datetest 1.27 {julianday('2001-01-01 12:59:60')} NULL
> +datetest 1.27 {julianday('2001-01-01 12:59:61')} NULL
>  datetest 1.28 {julianday('2001-00-01')} NULL
>  datetest 1.29 {julianday('2001-01-00')} NULL
> +datetest 1.30 {julianday('2001-01-01 12:59:60')} 2451911.0417
>
>  datetest 2.1 datetime(0,'unixepoch') {1970-01-01 00:00:00}
>  datetest 2.1b datetime(0,'unixepoc') NULL
>  datetest 2.1c datetime(0,'unixepochx') NULL
>  datetest 2.1d datetime('2003-10-22','unixepoch') NULL
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users