dates, times and R

classic Classic list List threaded Threaded
34 messages Options
12
Reply | Threaded
Open this post in threaded view
|

dates, times and R

Gabor Grothendieck
It's really useful that SQLite now supports window operations as that was
one of the key features that R users needed to do many manipulations
using SQLite.

From the perspective of the R language there is really one
particularly key feature left that prevents some users from easily
using SQLite in many cases.  That is the lack of date and time types.

From the perspective of the sqldf package in R, the user writes:

sqldf("select ...whatever...")

and sqldf creates an empty sqlite data base, looks for all table names
in the select statement, generates create table statements for them,
uploads the tables to the new database, runs the statement, downloads
the result and deletes the database. (This package uses a lower level
R driver package for sqlite which may also be used directly.)

R supports Date and POSIXct (date/time) classes which are represented
internally as days and seconds since the UNIX Epoch respectively;
however, due to the class it knows to display and manipulate them as
dates and datetimes rather than numbers.

If sqldf sends a Date or POSIXct to SQLite then it is sent as a number (days or
seconds since the UNIX Epoch) but when it is sent back it cannot know that
that number is supposed to represent a date or datetime.   There are some
default heuristics (if any of the input tables have a column name the same name
as an output column name then such output columns are automatically
converted) but this is far from foolproof.

This is not a problem for the other backend databases  that are
supported since those backends have true date and datetime types so
when R receives such objects it knows to convert them to R's similar types.
Currently SQLite is the default backend but I normally recommend that users
switch to the java based H2 backend if they are doing a lot of date and datetime
processing for the above reason. A lot of data analysis does involve
dates and date times so this covers a lot of applications.

If SQLite were to support true date and datetime types, as do other databases,
and not just functions which handle numbers as if they were dates or
datetimes this problem would be addressed so this is a key remaining feature
that I think SQLite needs.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: dates, times and R

Simon Slavin-3
On 11 Aug 2019, at 2:45pm, Gabor Grothendieck <[hidden email]> wrote:

> R supports Date and POSIXct (date/time) classes which are represented
> internally as days and seconds since the UNIX Epoch respectively;
> however, due to the class it knows to display and manipulate them as
> dates and datetimes rather than numbers.
>
> If sqldf sends a Date or POSIXct to SQLite then it is sent as a number (days or seconds since the UNIX Epoch) but when it is sent back it cannot know that that number is supposed to represent a date or datetime.   There are some default heuristics (if any of the input tables have a column name the same name as an output column name then such output columns are automatically converted) but this is far from foolproof.

Add a new heuristic that if the column name ends in '_Date' or '_
POSIXct' then the value should be converted.  When R creates a new SQLite table, it should append '_Date' or '_POSIXct' to the column name it chooses for classes of those types.  Existing heuristics should continue to work.

I do understand the value of having date/time types in SQLite, but it is not easy to do while retaining backward compatibility.  It'll have to wait for SQLite4 or something.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: dates, times and R

Gabor Grothendieck
Actually sqldf has multiple heuristics and the one you suggested is
already one of them
(except for minor differences in syntax) but this has the disadvantage
that the user must
specify classes whereas if the user simply uses any of the other backends
 they don't have to.

On Sun, Aug 11, 2019 at 10:52 AM Simon Slavin <[hidden email]> wrote:

>
> On 11 Aug 2019, at 2:45pm, Gabor Grothendieck <[hidden email]> wrote:
>
> > R supports Date and POSIXct (date/time) classes which are represented
> > internally as days and seconds since the UNIX Epoch respectively;
> > however, due to the class it knows to display and manipulate them as
> > dates and datetimes rather than numbers.
> >
> > If sqldf sends a Date or POSIXct to SQLite then it is sent as a number (days or seconds since the UNIX Epoch) but when it is sent back it cannot know that that number is supposed to represent a date or datetime.   There are some default heuristics (if any of the input tables have a column name the same name as an output column name then such output columns are automatically converted) but this is far from foolproof.
>
> Add a new heuristic that if the column name ends in '_Date' or '_
> POSIXct' then the value should be converted.  When R creates a new SQLite table, it should append '_Date' or '_POSIXct' to the column name it chooses for classes of those types.  Existing heuristics should continue to work.
>
> I do understand the value of having date/time types in SQLite, but it is not easy to do while retaining backward compatibility.  It'll have to wait for SQLite4 or something.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



--
Statistics & Software Consulting
GKX Group, GKX Associates Inc.
tel: 1-877-GKX-GROUP
email: ggrothendieck at gmail.com
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Backward compatibility vs. new features (was: Re: dates, times and R)

Thomas Kurz
In reply to this post by Simon Slavin-3
> I do understand the value of having date/time types in SQLite, but it is not easy to do while retaining backward compatibility.  It'll have to wait for SQLite4 or something.

Actually I do not really understand the point about backward compatibility. Many very useful suggestions are rejected by just citing "backward comatibility".

From my point of view, this is not actually a knock-out-criterium, because:

a) Existing applications would always continue to work, even if using newer versions of sqlite.dll as it should be no problem for any later version that intruduced feature X to continue using any database regardless of whether or not this database actually contains feature X. (This is actual *backward* compatibility.)

b) New applications could decide whether or not to make use of any new feature.

c) Of course, an existing application doesn't know how to handle database structures with feature X when using an sqlite.dll from the time before this feature has been introduced. (I would, however, call this *forward* compatibility.) This is true, but on the other hand, one might ask why an arbitrary application actually might want to do this? I have often gotten the response that it is up to the app how to handle data when reading from a database (IIRC, DATE as a matter of fact was the topic of the discussion). So one could as well argue that it is the app's responsibility to use up-to-date libraries when accessing databases. (Note that this applies *only* to an app dealing with *foreign* databases where one anyhow needs to know how to interpret data, so this is no knock-out-problem.)

Someone recently posted about SQLite support for the next 31 (or so) years. Actually I hope this doesn't mean we will have to wait for three decades until new features could be implemented...?!

Maybe a new subpage could be added to the website, named "proposed features" or similar, just listing what has been proposed including some short description. There have been many great ideas and it would be a pity if they got lost in the depths of the mailing list ;)

Just my 2cts
Thomas

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Backward compatibility vs. new features (was: Re: dates, times and R)

Richard Damon
On 8/11/19 4:21 PM, Thomas Kurz wrote:
>> I do understand the value of having date/time types in SQLite, but it is not easy to do while retaining backward compatibility.  It'll have to wait for SQLite4 or something.
> Actually I do not really understand the point about backward compatibility. Many very useful suggestions are rejected by just citing "backward comatibility".
>
> From my point of view, this is not actually a knock-out-criterium, because:
>
> a) Existing applications would always continue to work, even if using newer versions of sqlite.dll as it should be no problem for any later version that intruduced feature X to continue using any database regardless of whether or not this database actually contains feature X. (This is actual *backward* compatibility.)

The issue for something like a data-time field is how would you indicate
that a field is a data-time field. Due to backwards compatibility it
can't use the term data or time to trigger that use, as existing
applications use that and expect a different result, based on published
and promised rules.

>
> b) New applications could decide whether or not to make use of any new feature.
>
> c) Of course, an existing application doesn't know how to handle database structures with feature X when using an sqlite.dll from the time before this feature has been introduced. (I would, however, call this *forward* compatibility.) This is true, but on the other hand, one might ask why an arbitrary application actually might want to do this? I have often gotten the response that it is up to the app how to handle data when reading from a database (IIRC, DATE as a matter of fact was the topic of the discussion). So one could as well argue that it is the app's responsibility to use up-to-date libraries when accessing databases. (Note that this applies *only* to an app dealing with *foreign* databases where one anyhow needs to know how to interpret data, so this is no knock-out-problem.)
>
> Someone recently posted about SQLite support for the next 31 (or so) years. Actually I hope this doesn't mean we will have to wait for three decades until new features could be implemented...?!
New features can, and have, been added. The key is that they need to use
syntax that previously was an error (or at least without defined
meaning) to implement it.
>
> Maybe a new subpage could be added to the website, named "proposed features" or similar, just listing what has been proposed including some short description. There have been many great ideas and it would be a pity if they got lost in the depths of the mailing list ;)
>
> Just my 2cts
> Thomas

--
Richard Damon

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Backward compatibility vs. new features (was: Re: dates, times and R)

Thomas Kurz
> The issue for something like a data-time field is how would you indicate
that a field is a data-time field. Due to backwards compatibility it
can't use the term data or time to trigger that use, as existing
applications use that and expect a different result, based on published
and promised rules.

I don't see a huge problem here. Does the database file have a version number indicating which version the file has been created with? If so, newer SQLite libraries could easily emulate the old-style behavior by just checking this version number.

Alternatively, one could introduce a pragma statement, say PRAGMA emulation=ver, that could default to 3.30 (or whatever) now and which doesn't change at all (or will at least always be some years behind the most current version) and which tells the library to behave exactly as in version <ver>. This would allow changing modes without breaking existing applications. One step more could be storing <ver> in the database as well, using the version number enabled upon database creation. This way, one would have a mechanism that could eliminate "cannot be done due to backward compatibility" once and forever :)

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: dates, times and R

Keith Medcalf
In reply to this post by Gabor Grothendieck

On Sunday, 11 August, 2019 07:45, Gabor Grothendieck <[hidden email]> wrote:

>R supports Date and POSIXct (date/time) classes which are represented
>internally as days and seconds since the UNIX Epoch respectively;
>however, due to the class it knows to display and manipulate them as
>dates and datetimes rather than numbers.

SQLite3 can store integers and doubles.  Seems to me this is quite sufficient for storing a "number of seconds" since the Unix Epoch or "number of days" since the Unix epoch.  What exactly is the problem?  An IEEE-754 double precision floating point number is certainly capable of storing all offsets from the Unix Epoch (either as a days or seconds offset) with far more precision that a human is likely able to discern (or with more precision than the accuracy of most atomic clocks, for that matter).

>If sqldf sends a Date or POSIXct to SQLite then it is sent as a
>number (days or seconds since the UNIX Epoch) but when it is sent
>back it cannot know that that number is supposed to represent a
>date or datetime.

Why not?  The column type declaration in the table is merely an "arbitrary string", and the returned column names from a select are merely "arbitrary strings".  It seems like an "application deficiency" that it cannot set and retrieve "arbitrary metadata" for its own internal use (such as providing column type declarations in the CREATE TABLE or additional metadata in the column name (in select statements).  Many other "applications" do so without difficulty (for example the default sqlite3 wrapper in Python).  Are the R programmers somehow particularly deficient in this regard?

I quite often store "timestamps" using application dependent epochs and offsets without difficulty (for example, the number of one-minute intervals since the Unix epoch).  Given that just about every programming system and language ever invented seems to store datetime data as some type of offset from some epoch, and each of them different, does not seem to bother interoperability in the least.  Once you know the Epoch and Offset interval, conversion is rather simple arithmetic that most children learned in primary school.

--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.







_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Backward compatibility vs. new features (was: Re: dates, times and R)

Simon Slavin-3
In reply to this post by Thomas Kurz
On 11 Aug 2019, at 11:51pm, Thomas Kurz <[hidden email]> wrote:

> I don't see a huge problem here. Does the database file have a version number indicating which version the file has been created with? If so, newer SQLite libraries could easily emulate the old-style behavior by just checking this version number.

SQLite database files are compatible from version 3.0.0 introduced in 2004.  This includes everything except for tables using the WITHOUT ROWID feature, introduced in 2013.

> Alternatively, one could introduce a pragma statement, say PRAGMA emulation=ver, that could default to 3.30 (or whatever) now and which doesn't change at all

This would break backward compatibility.  It is necessary to be sure that database files made with current versions of SQLite can be opened with old versions back to 2013.

The things you're thinking of are not bad, they're good.  But people have suggested them before and we've always found problems with them.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Backward compatibility vs. new features (was: Re: dates, times and R)

Keith Medcalf
In reply to this post by Richard Damon

On Sunday, 11 August, 2019 16:02, Richard Damon <[hidden email]> wrote:

>On 8/11/19 4:21 PM, Thomas Kurz wrote:

>>> I do understand the value of having date/time types in SQLite, but
>>> it is not easy to do while retaining backward compatibility.  

I do not see any value in having a date/time type is SQLite.  I do not see any value whatsoever that cannot be met by the existing datatypes (though an extensible type system would be nice, if I really want one of those I can use a database that has one, the only one I know of being DB2).

What "value" would a built-in date/time type have?  What possible utility could it provide that does not already exist?

Even if one were to add one, it would be fraught with peril since it would undoubtedly be implemented badly.  Almost all date/time types in almost every existing Operating System or language or database system is implemented badly and it would be far better off if this sort of thing did not exist at all because for almost all practical uses the "builtin" crap has to be ignored and re-written to operate properly.  Or you have to incorporate massive amounts of code to work around the deficiencies.

Then you have the crap Operating Systems that are inherently defective in their handling and concept of date/time (which are most of them).  And you have to write your own code to do it properly anyway.  And that sort of defeats the whole purpose of a lite embedded database engine if it has to be overloaded with a massive amount of code to handle date/time properly -- possibly duplicating at great expense what already exists elsewhere.  For example, on *some* combinations of Operating System and Language you would have the same code to do "proper" date/time handling duplicated three times, once in the OS, once in the programming language standard library, and once in the database engine.  Then of course every third-party library would also have its own duplicated code base to do the same thing.  And that is assuming that everyone's definition of "properly" is the same (which it definitely will not be).

>>> It'll have to wait for SQLite4 or something.

>> Actually I do not really understand the point about backward
>> compatibility. Many very useful suggestions are rejected by just
>> citing "backward comatibility".

As I said previously this is a non-issue unless one wants to add a new type to be dealt with internally (which is not needed).  The table column datatype is an arbitrary string.  You can put in there whatever you like and interpret it accordingly.  Mutatis mutandis the column names returned from a select statement.  So while it may be of some (questionable) value to add additional datatypes like decimal (decimal32/decimal64/decimal128 etc), different float widths (float16, float32, float64, float128 etc), or different integer types (int8, int16, int43, int64, int128 etc., and the unsigned varients) these do not provide a whole lot of additional value (though the decimalXX types might).  An extensible arbitrary-type system would be more valuable.

Since date/time is stored as an offset in some units from an epoch of some type, the "datatype" declaration is nothing more than an annotation of an already existing double or integer type -- and you can already annotate your select column names and table attribute type declarations just fine.

--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.




_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Backward compatibility vs. new features (was: Re: dates, times and R)

D Burgess
In applications dates/times are input, dates/times are output.
Commonly the storage format of dates/times is of no concern.
More effort is often spent on time zone display and input, which is an
application issue rather than a data store issue. (e.g. fossil)
All one *needs* is database functions to input what you output (and
vice versa),
For me, the major benefit of a database "date/time" types is clarity
for humans when reading the schema.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Backward compatibility vs. new features (was: Re: dates, times and R)

Tim Streater-3
In reply to this post by Keith Medcalf
On 12 Aug 2019, at 00:43, Keith Medcalf <[hidden email]> wrote:

> On Sunday, 11 August, 2019 16:02, Richard Damon <[hidden email]>
> wrote:
>
>>On 8/11/19 4:21 PM, Thomas Kurz wrote:
>
>>>> I do understand the value of having date/time types in SQLite, but
>>>> it is not easy to do while retaining backward compatibility.  
>
> I do not see any value in having a date/time type is SQLite.

Neither do I. The correct way is to store, as an integer, seconds (or whatever time unit you want) since some epoch. The display of what that stored value means is a *presentation* issue and should be handled as such by whatever language is presenting to the user. That also allows the program to either allow the user to presentation format to suit themselves, or for the program to follow the way system prefs are set.


--
Cheers  --  Tim
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Backward compatibility vs. new features (was: Re: dates, times and R)

Thomas Kurz
In reply to this post by Keith Medcalf
> Since date/time is stored as an offset in some units from an epoch of some type, the "datatype" declaration is nothing more than an annotation of an already existing double or integer type -- and you can already annotate your select column names and table attribute type declarations just fine.

The problem is not only about storing an arbitrary integer or float number. A date is much more, it has timezone information with it, and I would like to see a DATE column handle this in a proper and well-defined way, just as a calendar (CalDAV) does handle it, so it would allow me to convert between e.g. EST and CEST or calculating time differences (e.g. working time in a company). I know many of this is somehow possible now as well, but as far as I know, not really compliant with other RDMSs.

It's not only the date type itself, I miss some other useful aids as well. MariaDB/MySQL for example have the "DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP" feature, useful for having a "last modified" column to track changes. SQLite requires me to define a trigger which is quite complex as one has to declare each column except for the "last modified" one to avoid a recursive trigger.

Beyond data/time, a great improvement would be support for geodata. SQLite has become quite popular for GIS applications (e.g. QGIS, but also ArcGIS) using Spatialite and/or GeoPackage. Currently, there is no GEOMETRY data type so both store geometries in BLOBs ensuring proper functionality with a whole bunch of triggers. This leads to many problems, for example when renaming tables or columns. I often had inconsistencies and needed to manually adjust the "geometry_columns" table (which also would be obsolete then, leading to much cleaner database layout) to make things working again.

Please apologize: I do not want to offend anyone, it's just my opinion. I appreciate SQLite being a great thing and I don't know a better embedded database (one can forget about Firebird, etc.). But in my everyday work I always find some issues that could make a great thing perfect if they were implemented :-)

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: dates, times and R

Gabor Grothendieck
In reply to this post by Keith Medcalf
The whole point of this is to make it as easy as possible for the user.
With other backends the database handles the types but with sqlite
the user has to get involved.

It is not a matter of storage.  It is a matter of maintaining the type
information
on the database side and passing the type information back to R.

Perhaps an example would help.  Consider this.  Here R passes the
type information to the H2 database backend and H2 passes it back to R
so that the output is correctly typed.  This works as desired.

  library(RH2) # load H2 driver and H2 database
  library(sqldf) # load package

  DF <- data.frame(d = as.Date("2000-01-01"))  # create data.frame
with one column d

  sqldf("select d, d + 1 as nextDay from DF")
  ##            d    nextDay
  ## 1 2000-01-01 2000-01-02

Now if we switch from H2 to sqlite that would have not have worked.
Any of these workaournds
are currently implemented and work but are not optimal.

  library(sqldf)  # if no driver loaded it assumes sqlite

  # Alternative 1
  # method = "Date" means all returned numbers are regarded as Date

  sqldf("select d, d + 1 as nextDay from DF", method = "Date")
  ##            d    nextDay
  ## 1 2000-01-01 2000-01-02

 # Alternative 2
  # this is similar to what Simon suggested.
  # method = "name__class" means use the column name suffix
  # to determine the type

  sqldf("select d as d__Date, d + 1 as nextDay__Date from DF", method
= "name__class")
  ##            d    nextDay
  ## 1 2000-01-01 2000-01-02

 # Alternative 3
  # convert it manually.  It deduces that d is Date because an input
  # column of the same name exists as Date but that is not foolproof
  # and does not, for example, work for the nextDay column so we
  # convert it manually

  out <- sqldf("select d, d + 1 as nextDay from DF")
  out$nextDay <- as.Date(out$nextDay, origin = "1970-01-01")
  out
  ##            d    nextDay
  ## 1 2000-01-01 2000-01-02

On Sun, Aug 11, 2019 at 7:16 PM Keith Medcalf <[hidden email]> wrote:

>
>
> On Sunday, 11 August, 2019 07:45, Gabor Grothendieck <[hidden email]> wrote:
>
> >R supports Date and POSIXct (date/time) classes which are represented
> >internally as days and seconds since the UNIX Epoch respectively;
> >however, due to the class it knows to display and manipulate them as
> >dates and datetimes rather than numbers.
>
> SQLite3 can store integers and doubles.  Seems to me this is quite sufficient for storing a "number of seconds" since the Unix Epoch or "number of days" since the Unix epoch.  What exactly is the problem?  An IEEE-754 double precision floating point number is certainly capable of storing all offsets from the Unix Epoch (either as a days or seconds offset) with far more precision that a human is likely able to discern (or with more precision than the accuracy of most atomic clocks, for that matter).
>
> >If sqldf sends a Date or POSIXct to SQLite then it is sent as a
> >number (days or seconds since the UNIX Epoch) but when it is sent
> >back it cannot know that that number is supposed to represent a
> >date or datetime.
>
> Why not?  The column type declaration in the table is merely an "arbitrary string", and the returned column names from a select are merely "arbitrary strings".  It seems like an "application deficiency" that it cannot set and retrieve "arbitrary metadata" for its own internal use (such as providing column type declarations in the CREATE TABLE or additional metadata in the column name (in select statements).  Many other "applications" do so without difficulty (for example the default sqlite3 wrapper in Python).  Are the R programmers somehow particularly deficient in this regard?
>
> I quite often store "timestamps" using application dependent epochs and offsets without difficulty (for example, the number of one-minute intervals since the Unix epoch).  Given that just about every programming system and language ever invented seems to store datetime data as some type of offset from some epoch, and each of them different, does not seem to bother interoperability in the least.  Once you know the Epoch and Offset interval, conversion is rather simple arithmetic that most children learned in primary school.
>
> --
> The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Backward compatibility vs. new features (was: Re: dates, times and R)

Tim Streater-3
In reply to this post by Thomas Kurz
On 12 Aug 2019, at 12:41, Thomas Kurz <[hidden email]> wrote:

> The problem is not only about storing an arbitrary integer or float number. A
> date is much more, it has timezone information with it, and I would like to
> see a DATE column handle this in a proper and well-defined way, just as a
> calendar (CalDAV) does handle it, so it would allow me to convert between e.g.
> EST and CEST or calculating time differences (e.g. working time in a company).
> I know many of this is somehow possible now as well, but as far as I know, not
> really compliant with other RDMSs.

I don't expect to do that with SQL. My "seconds since the epoch" is based on converting any particular time to GMT and storing that. That number is then converted to a date/time with TZ info for display.


--
Cheers  --  Tim
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: dates, times and R

Graham Holden
In reply to this post by Gabor Grothendieck
Monday, August 12, 2019, 1:06:00 PM, Gabor Grothendieck <[hidden email]> wrote:

> The whole point of this is to make it as easy as possible for the user.
> With other backends the database handles the types but with sqlite
> the user has to get involved.

> ...

>   sqldf("select d as d__Date, d + 1 as nextDay__Date from DF", method
> = "name__class")
>   ##            d    nextDay
>   ## 1 2000-01-01 2000-01-02

NOTE: I don't know R, so I may be talking rubbish...

I *think* the idea of whoever suggested it was not that the *user*
should have to do things like the above, but that the INTERFACE
between R and SQLite should do it. In my simplistic view the interface
"knows" the input is a date ("as.Date(..)") and so would tack the
"...__date" hint on to the column name when talking to SQLite. Coming
back out of SQLite, the interface would recognize the "...__date" and
turn the value into an R date.

Graham Holden



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Backward compatibility vs. new features (was: Re: dates, times and R)

Simon Slavin-3
In reply to this post by Tim Streater-3
On 12 Aug 2019, at 1:27pm, Tim Streater <[hidden email]> wrote:

> I don't expect to do that with SQL. My "seconds since the epoch" is based on converting any particular time to GMT and storing that. That number is then converted to a date/time with TZ info for display.

I'm with Tim.  Storing the time zone with the timestamp is a different matter.  It leads to problems with sorting and searching.  We can discuss it, but it doesn't belong in this thread.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: dates, times and R

Gabor Grothendieck
In reply to this post by Graham Holden
Thanks for the idea but the interface already handles that and does it
without special names. The last example in my last post shows that
d was correctly typed in the output because the interface noticed that
it had the same name as an input column.  Other problems are that it
would still not handle propagation through expressions and would require
that the user use special names different than the names in the
input.

I appreciate these ideas but these or equally effective alternatives are
already implemented and it is precisely these kludges that I was
trying to avoid.  With one R statement the user can switch back ends
so unless sqlite works as smoothly as the alternative backends
a user will choose one of those if they are doing a lot of date and
datetime processing.

For many applications the other advantages
of sqlite would take precedence.
The fact that sqlirte is included right in
the R driver package is very convenient as it means there is nothing
additional to install
beyond the R driver.  (H2 is also included in the R driver but in that
case java needs to be installed.) Also the new windowing functions, CTEs
and other features are great.
Unfortunately in the widely applicable case of dates and date times
the other databases just work and additional care needs to be taken
with sqlite.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Backward compatibility vs. new features (was: Re: dates, times and R)

J Decker
In reply to this post by Simon Slavin-3
On Mon, Aug 12, 2019 at 5:42 AM Simon Slavin <[hidden email]> wrote:

> On 12 Aug 2019, at 1:27pm, Tim Streater <[hidden email]> wrote:
>
> > I don't expect to do that with SQL. My "seconds since the epoch" is
> based on converting any particular time to GMT and storing that. That
> number is then converted to a date/time with TZ info for display.
>
>
If the timezone is stored, then the time is all UTC and easily sortable.
 A sub-order of timeone within a sepcific time sequence ends up happening
*shrug*

But then, I'm assuming the time would just be ISO8601; since SQLite
datetime functions take that as an input already.



> I'm with Tim.  Storing the time zone with the timestamp is a different
> matter.  It leads to problems with sorting and searching.  We can discuss
> it, but it doesn't belong in this thread.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Backward compatibility vs. new features (was: Re: dates, times and R)

Tim Streater-3
On 12 Aug 2019, at 14:30, J Decker <[hidden email]> wrote:

> On Mon, Aug 12, 2019 at 5:42 AM Simon Slavin <[hidden email]> wrote:
>
>> On 12 Aug 2019, at 1:27pm, Tim Streater <[hidden email]> wrote:
>>
>> > I don't expect to do that with SQL. My "seconds since the epoch" is
>> based on converting any particular time to GMT and storing that. That
>> number is then converted to a date/time with TZ info for display.

> If the timezone is stored, then the time is all UTC and easily sortable.
>  A sub-order of timeone within a sepcific time sequence ends up happening
> *shrug*

Why are you storing the timezone? You display the TZ of the user who is, later, viewing the data. And that user could be anywhere.

> But then, I'm assuming the time would just be ISO8601; since SQLite
> datetime functions take that as an input already.

I'm a user; I don't want my times displayed as ISO8601. That's why we have date/time control panels so the user gets to choose how those are displayed.


--
Cheers  --  Tim
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Backward compatibility vs. new features (was: Re: dates, times and R)

Thomas Kurz
In reply to this post by Simon Slavin-3
> This would break backward compatibility.  It is necessary to be sure that database files made with current versions of SQLite can be opened with old versions back to 2013.

This is what I would call "forward compatibility": You expect an old application to be able to read file formats of a future version. Do you have an example where there is really required? Apart from database browsers I cannot think of many situations where an application has to read an arbitrary database created by an arbitrary application. It wouldn't know how to interpret data anyhow?

> > Alternatively, one could introduce a pragma statement, say PRAGMA emulation=ver, that could default to 3.30 (or whatever) now and which doesn't change at all

> This would break backward compatibility.  It is necessary to be sure that database files made with current versions of SQLite can be opened with old versions back to 2013.

I don't think it would break compatibility. If there's no space to include version information in the database file directly, it could e.g. be added to the table definition: CREATE TABLE whatsoever VERSION=3.30, just like the ENGINE keyword of MariabDB. Reading tables without a VERSION keyword are automatically interpreted in the version where this feature was introduced first.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
12