Re: [sqlite] Backward compatibility vs. new features (was: Re: dates, times and R)
Another reason: because PostgreSQL supports it as well (including timezone) ;-) - Original Message - From: Peter da Silva To: SQLite mailing list Sent: Tuesday, August 13, 2019, 23:18:29 Subject: [sqlite] Backward compatibility vs. new features (was: Re: dates, times and R) If the datr/time is stored internally as utc iso8601 text then it will remain compatible with old versions and can implement whatever new behavior is needed on new versions. The bigger question is 'what new behavior'? The only nee behavior seems to be 'let this third party package see it as a date', which it should be able to figure out by looking at the schema. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Backward compatibility vs. new features (was: Re: dates, times and R)
Yep, I agree, to which is where I pointed out that you'd need the additional information for that store location. But that's all you'd need, and only in that one location. Your UI (Or whatever specialized report generation) would have to do the math from the UTC time, and convert it accordingly to whatever the stores location TZ info is, then present the appropriate information. So even if you are in BC, you can look at NFLD info and get the NFLD local times. On Wed, Aug 14, 2019 at 12:24 PM Warren Young wrote: > On Aug 14, 2019, at 9:55 AM, Stephen Chrzanowski > wrote: > > > > On Tue, Aug 13, 2019 at 7:30 PM J Decker wrote: > > > >>> 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. > >> > >> Because the actual time on the clock on the wall matters. > >> > > The time on the clock is irrelevant, completely and totally, due to > > daylight saving. > > I agree with your reply as far as it goes, and I agree that times stored > in the DB should be in UTC or some similar format. > > (By that I mean we don’t need to get into UTC vs UT1 vs whatever other > “universal” time standard you prefer. Just pick one and stick to it for > all users of that DB.) > > However, it *can* be helpful to store the time zone or the local UTC > offset alongside the universal timestamp in the DB so you can translate the > timestamp to local time in calculations. Without it, you can’t ask the DB > questions like, “Give me all records that occurred between 9am and 10am > local time,” or “Across all stores, what are our busiest hours?” > > The timestamp in UTC lets you ask different questions, such as “Give me > the 10 most recent matching records,” which breaks if you store timestamps > as local time, and your DB holds values from multiple time zones. > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Backward compatibility vs. new features (was: Re: dates, times and R)
On Aug 14, 2019, at 9:55 AM, Stephen Chrzanowski wrote: > > On Tue, Aug 13, 2019 at 7:30 PM J Decker wrote: > >>> 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. >> >> Because the actual time on the clock on the wall matters. >> > The time on the clock is irrelevant, completely and totally, due to > daylight saving. I agree with your reply as far as it goes, and I agree that times stored in the DB should be in UTC or some similar format. (By that I mean we don’t need to get into UTC vs UT1 vs whatever other “universal” time standard you prefer. Just pick one and stick to it for all users of that DB.) However, it *can* be helpful to store the time zone or the local UTC offset alongside the universal timestamp in the DB so you can translate the timestamp to local time in calculations. Without it, you can’t ask the DB questions like, “Give me all records that occurred between 9am and 10am local time,” or “Across all stores, what are our busiest hours?” The timestamp in UTC lets you ask different questions, such as “Give me the 10 most recent matching records,” which breaks if you store timestamps as local time, and your DB holds values from multiple time zones. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Backward compatibility vs. new features (was: Re: dates, times and R)
On Tue, Aug 13, 2019 at 7:30 PM J Decker wrote: > > > 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. > > Because the actual time on the clock on the wall matters. > I want to know cashiers that are making transactions outside of 9am-5pm > (for instance). But depending on where they are that time is different. > I also want to know the time continuously according to the linear time that > they happened in. > > The time on the clock is irrelevant, completely and totally, due to daylight saving. Moving the clock back and forth twice a year causes a chunk of time to go missing or a chunk of time to be duplicated. The clock is a User Interface problem, not a data storage problem. When you want to know that locations actual time, you store that stores location with their TZ information, and your UI presents that information to you. Because UTC does not move forward or backwards (Occasionally we add a second due to the rotation of the earth slowing down*, but we NEVER go backwards), anything time related in a database NEEDS to be stored in UTC/Zulu/GMT-0 time (Or whatever you want to call it). This is THE base time. Sure, you COULD store the location, but, if you're in British Columbia, looking at a stores transactions in New Found Land, (Or say California versus New York), what time do you believe? I work for a company that literally has servers all around the world, which means Time Zones ARE a thing for us. We set every single machine we deploy to UTC. If there were any times to be displayed in "local" time, it's handled at the application/presentation layer, not the database layer. *https://www.quora.com/Why-is-Earths-rotation-slowing-down -- First hit on Google. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Backward compatibility vs. new features (was: Re: dates, times and R)
On Mon, Aug 12, 2019 at 6:54 AM Tim Streater wrote: > On 12 Aug 2019, at 14:30, J Decker wrote: > > > On Mon, Aug 12, 2019 at 5:42 AM Simon Slavin > wrote: > > > >> On 12 Aug 2019, at 1:27pm, Tim Streater 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. Because the actual time on the clock on the wall matters. I want to know cashiers that are making transactions outside of 9am-5pm (for instance). But depending on where they are that time is different. I also want to know the time continuously according to the linear time that they happened in. > > 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. > > Then strftime it. I don't want to see PK/FK identifiers either. > > -- > Cheers -- Tim > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Backward compatibility vs. new features (was: Re: dates, times and R)
If the datr/time is stored internally as utc iso8601 text then it will remain compatible with old versions and can implement whatever new behavior is needed on new versions. The bigger question is 'what new behavior'? The only nee behavior seems to be 'let this third party package see it as a date', which it should be able to figure out by looking at the schema. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Backward compatibility vs. new features (was: Re: dates, times and R)
> > > 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? > > > > I have an Android app that lets you share the database between users. The > > app will run on a variety of versions of Android with a similar variety > > of versions of sqlite. Currently any version can read the database from > > any other > > version regardless of whether it is old reading new or vice versa. > > > > Then if you want to retain that, just don't use new features that might > break fwd-compatibility. Yes, I am aware of this. I was just providing a "real" example to answer the question posed by the OP. Regards Andy Ling ** DISCLAIMER: Privileged and/or Confidential information may be contained in this message. If you are not the addressee of this message, you may not copy, use or deliver this message to anyone. In such event, you should destroy the message and kindly notify the sender by reply e-mail. It is understood that opinions or conclusions that do not relate to the official business of the company are neither given nor endorsed by the company. Thank You. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Backward compatibility vs. new features (was: Re: dates, times and R)
On Tue, Aug 13, 2019 at 10:58 AM Ling, Andy wrote: > > 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? > > I have an Android app that lets you share the database between users. The > app will run on a variety of versions of Android with a similar variety > of versions of sqlite. Currently any version can read the database from > any other > version regardless of whether it is old reading new or vice versa. > Then if you want to retain that, just don't use new features that might break fwd-compatibility. That's already the case now, e.g. without-rowid tables, if used, break older versions of SQLite. Or defining a view or a trigger using window-functions. Or zillions other things that can make a DB incompatible with old versions of SQLite. That can happen now! And to gracefully handle cases of incompatibilities "by choice", your app should use https://www.sqlite.org/pragma.html#pragma_user_version to detect such cases, and/or have a user setting to save in "compatibility mode", foregoing better/new features, or not. Of course, the version of the runtime SQLite library can also be used. The fact the SQLite file-format has little to no wiggle room for changes, does *NOT* mean SQLite won't or can't grow new features. It's growing them all the time. The "for the next 35-years" comment was made by DRH himself, and do not mean there won't be new things in SQLite for that time, but instead that DRH and team commit (pun intended :)) to supporting all existing and future features until 2050 at the least (modulo the BUS FACTOR of course...), and that commitment is backed by SQLite's outstanding 100% line and branch coverage testing, and is one of the primary reason DRH is always reluctant to add new requested features (quite a testing burden). But don't despair, FKs, Function-based Indexes, CTEs, Window-Functions, etc... were asked literally for years, and made it eventually. When you have a long view like Richard does, you're not in a rush to do anything I guess :). --DD ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Backward compatibility vs. new features (was: Re: dates, times and R)
> 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? I have an Android app that lets you share the database between users. The app will run on a variety of versions of Android with a similar variety of versions of sqlite. Currently any version can read the database from any other version regardless of whether it is old reading new or vice versa. Regards Andy Ling ** DISCLAIMER: Privileged and/or Confidential information may be contained in this message. If you are not the addressee of this message, you may not copy, use or deliver this message to anyone. In such event, you should destroy the message and kindly notify the sender by reply e-mail. It is understood that opinions or conclusions that do not relate to the official business of the company are neither given nor endorsed by the company. Thank You. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Backward compatibility vs. new features (was: Re: dates, times and R)
On Monday, 12 August, 2019 13:52, Simon Slavin wrote: >On 12 Aug 2019, at 8:20pm, Thomas Kurz wrote: >> c) To enable the new DATE interpreation, we do: >> CREATE TABLE TEST VERSION=3.34 --> will be stored the same way >And now you have a file which can't be edited with old versions of >the CLI. However you cut it, you have compatibility problems. These issues already exist in the current codebase. For example, older versions of SQLite3 have no clue what to do with things like indexes on expressions, conditional indexes, or even descending indexes. If such a database is attempted to be accessed by a version of SQLite3 that does not understand the features, the database is treated as being corrupt. However, since those features merely "overload" existing capabilities, removing them renders the database useable by those earlier versions. Similarly, I suppose one could "uverload" a standard float or integer type to handle datetime data and this would not affect the underlying database structure, but would merely be seen as "corruption" by a version of SQLite3 that does not know how to parse the new sugar. Removing the sugar would render the database once more accessible by the older versions. -- 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Backward compatibility vs. new features (was: Re: dates, times and R)
> And now you have a file which can't be edited with old versions of the CLI. > However you cut it, you have compatibility problems. One shouldn't do it at all. It's like trying to a edit a DOCX with Word95. It's not *backward* compatibility. It's not the case you mentioned before. And finally, when using the CLI there's no reason not to update the CLI. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Backward compatibility vs. new features (was: Re: dates, times and R)
On 12 Aug 2019, at 8:20pm, Thomas Kurz wrote: > c) To enable the new DATE interpreation, we do: > CREATE TABLE TEST VERSION=3.34 --> will be stored the same way And now you have a file which can't be edited with old versions of the CLI. However you cut it, you have compatibility problems. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Backward compatibility vs. new features (was: Re: dates, times and R)
> A programmer uses a copy of the SQLite CLI to correct errors in a database > made and maintained by a production program. This involves making a new > table, copying some data from the old data to the new table, deleting the old > table, then renaming the new table. When the programmer is finished making > changes, they replace the old version of the database with the corrected > version. But this ain't a problem because the default would be a compatible version. Maybe I should explain my idea a bit more explicit, to avoid a misunderstanding. Let's assume preparations with 3.31 and the first real change (e.g. DATE) in 3.34. For simplicity, we create a table TEST without any declaration. Application itself uses 3.16. a) Creating table with any version <3.31 results in: CREATE TABLE TEST b) Creating table with 3.31+ without any explicit declaration: CREATE TABLE TEST --> will be stored as CREATE TABLE TEST VERSION=3.31 This should be no problem for any library below 3.31 as it should ignore the VERSION declaration. Everything that is done with this table will always operate in 3.31 compatibility mode, regardless of what library version is actually being used, be it an old one or a new one. c) To enable the new DATE interpreation, we do: CREATE TABLE TEST VERSION=3.34 --> will be stored the same way Now we have to distinguish three cases: i) Library versions 3.34+ can use the modified DATE datatype. Reasonably, it should internally be encoded as INT or FLOAT, depending on what timestamp is internally being used. This will be a compatibility-fallback for case iii. Alternatively, one could use BLOB to tell case iii not to handle this data at all. ii) Versions 3.31, 3.32 and 3.33 should reject modifying such kind of table as (when being honest) they do not know about the feature. iii) Versions up to and including 3.30 should basically also reject modifications, but they cannot do it as they don't now about the VERSION flag. For this case, we have stored the data either as INT/FLOAT which can be handled, or as BLOB which wouldn't be touched. The important point is that we would have introduced a mechanism which should be future-proof, but breaking compatibility as few as possible. And a programmer would always have to actively enable a "feature level". If he didn't do that, the library would operate in compatibility mode as it is the case right now. Modifying tables would not be a problem either. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Backward compatibility vs. new features (was: Re: dates, times and R)
On 12 Aug 2019, at 6:01pm, Thomas Kurz wrote: >> 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? A programmer uses a copy of the SQLite CLI to correct errors in a database made and maintained by a production program. This involves making a new table, copying some data from the old data to the new table, deleting the old table, then renaming the new table. When the programmer is finished making changes, they replace the old version of the database with the corrected version. The production program, written three years ago, uses an old version of the SQLite library. The editing happens on the programmer's own computer which, naturally, has an up-to-date copy of the SQLite CLI, with an up-to-date copy of the SQLite library. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Backward compatibility vs. new features (was: Re: dates, times and R)
> 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Backward compatibility vs. new features (was: Re: dates, times and R)
On 12 Aug 2019, at 14:30, J Decker wrote: > On Mon, Aug 12, 2019 at 5:42 AM Simon Slavin wrote: > >> On 12 Aug 2019, at 1:27pm, Tim Streater 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Backward compatibility vs. new features (was: Re: dates, times and R)
On Mon, Aug 12, 2019 at 5:42 AM Simon Slavin wrote: > On 12 Aug 2019, at 1:27pm, Tim Streater 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 > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Backward compatibility vs. new features (was: Re: dates, times and R)
On 12 Aug 2019, at 1:27pm, Tim Streater 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Backward compatibility vs. new features (was: Re: dates, times and R)
On 12 Aug 2019, at 12:41, Thomas Kurz 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Backward compatibility vs. new features (was: Re: dates, times and R)
> 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Backward compatibility vs. new features (was: Re: dates, times and R)
On 12 Aug 2019, at 00:43, Keith Medcalf wrote: > On Sunday, 11 August, 2019 16:02, Richard Damon > 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Backward compatibility vs. new features (was: Re: dates, times and R)
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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Backward compatibility vs. new features (was: Re: dates, times and R)
On Sunday, 11 August, 2019 16:02, Richard Damon 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Backward compatibility vs. new features (was: Re: dates, times and R)
On 11 Aug 2019, at 11:51pm, Thomas Kurz 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Backward compatibility vs. new features (was: Re: dates, times and R)
> 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 . This would allow changing modes without breaking existing applications. One step more could be storing 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Backward compatibility vs. new features (was: Re: dates, times and R)
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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Backward compatibility vs. new features (was: Re: dates, times and R)
> 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
