2016-05-05 15:36 GMT+02:00 Adam Devita <adevita at verifeye.com>:
> What would be the 'correct' behaviour for an out of bounds day in a
> month? If you look at dates as an index + natural number offset then Jan
> 32 == Feb 1. What is January 0 or January -1?
>
?Well, if my memory is correct, that is the way MySQL does it. I liked
that, but because at the moment 32 is always an illegal value I only
propose to make 30 and 31 illegal for February. ?It would also be nice to
make 29 illegal when it is not a leap year. And 31 for months that only
have 30 days.
It is even possible with SQLite. See the end of the post.
If expressing dates this way (and not as an int from an epoch) I think
> that it is up to the application to sanitize inputs for this one. About
> 8 years ago I decided that I was not going to to use the sqlite date
> functions at all, preferring to either store string dates for humans or the
>
?I am not doing it either, but other people do.
?
> numeric value (unix epoch, or .net timestamp ), and write it into the
> design rules for the project that only UTC date values will be in the db.
>
?I also only work with UTC values. Makes thing a little more complicated,
but I do not mind.?
?For example I made yesterday a service that stores the CPU usage of
Firefox in a SQLite table every minute. When the day changes I want to
generate a summary. This means I have to ask the date from SQLite, but that
is not to difficult:
sqlite3 <<<"SELECT CURRENT_DATE;"
And again: I am only talking about UTC.
?
> Every once in a while there are some very interesting lessons about the
> precision of time on the list (the Earth used to turn faster) etc. Others
> can talk about extensions / modules handles in the extreme cases. I
> wouldn't suggest that we drop the date time functions due to a predictable
> storm of "we will not break backward compatibility", but I wouldn't care if
> sqlite dropped date formatting altogether. (At this point I do not need
> instructions as to compiling it out so save a few KB on the device.)
>
?Well, there are people using it. (And probably a lot.) So if with a little
effort the quality would significantly improve (and I am not talking about
making it perfect), then I think it would be worth the effort.
> People on the list are genuinely trying to be helpful, even if cross
> language text comes across as terse, try to keep that context.
>
?Well, I do not think it is cross language, but straw man. (I like neither,
but the second is even worse I think.) Saying that what I suggest is
impossible by pretending that I suggest something completely different.
Another thing is the discussion I started about NULL in primary keys. I was
a little bit to enthusiastic and I was wrong. I have no problem
acknowledging that. But also in this case it started wrongly. I was told
that I should read the documentation and not think that SQLite does the
things like other databases. I had read the documentation and the
documentation showed that the maintainers would like to prohibit NULL's in
primary keys. (That was why I started the discussion.)
If there is a reason to not do something, use real arguments. I do not mind
them and in the long run it is better for everyone. It takes initially a
little more time, but it is best in the long run. You can build a house
faster when you do not lay down a foundation, but I do not think it is a
good idea.
And I think the solution could even be reasonable simple. When executing:
?WITH datevalue AS (
SELECT '2004-04-31 23:59:00' dateString
)
SELECT dateString
, strftime('%Y-%m-%d %H:%M', dateString)
AS strftime
, strftime('%s', dateString)
AS epochtime
, strftime('%Y-%m-%d %H:%M', strftime('%s', dateString), 'unixepoch')
AS strftime2
FROM datevalue
;
you get:
?dateString strftime epochtime strftime2
------------------- ---------------- ---------- ----------------
2004-04-31 23:59:00 2004-04-31 23:59 1083455940 2004-05-01 23:59
By creating the epochtime you could get the ?real? date like in MySQL, but
this is then also the solution for validating strftime. Internally
strftime2 should also be done and if that gives a different value as
strftime, then strftime should return NULL.
That should not to hard to implement I would think.
?With an extra parameter strftime could be made to return ??2004-05-01
23:59? instead of null. That would be a nice enhancement also I think and
would not break backward compatibility.
Of-course it can not be implemented immediately, things has to be thought
over before doing something, to circumvent being bitten, but I think it is
worth it.
--
Cecil Westerhof
