Re: [sqlite] Subject: Re: SQL Date Import

[dmp]

> SQLite doesn't have a DATE type.  You can store dates in a SQLite
> database as text, or integers or floating point numbers (e.g. "20180602",
> a number of days, a number of seconds).  But when you ask for a value,
> that's what you'll get back.  Any interpretation of that value as a
> date is up to you or your software.

> Simon.

That is the real crux of the situation, I have a handle on how to display
DATEs whether they are NUMERIC or TEXT. Users really gather no meaning
from temporal values as numbers.

The real issue I suppose after some thought is the export/import of a
DATE, TIME, TIMESTAMP. Most Databases for both SQL, INSERT example,
and CSV is done with TEXT. Example being standard SQL DATE, -MM-DD.

I think that the case is to preserve the users data in the type they
have chosen for SQL export, have to test each entry for NUMERIC or TEXT.

The import of the SQL INSERT statements will return the database entries
to the same data. I have no control on those since they are passed
directly to SQLite.

CSV export will be TEXT, since that is the most likely way a spreadsheet
will recognize the data.

I will have to test probably import of CSV for NUMERIC or TEXT and not
just assume TEXT. A determination can be made of the typeof() for the
DATE field as NUMERIC or TEXT then transition the data accordingly if
need be.

danap.

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

Re: [sqlite] Subject: Re: SQL Date Import

[Jean-Christophe Deschamps]

The problem not having a DATETIME field is, however, very simple: When 
reading a foreign database which stores date values as a number, I 
have to guess on how to get back the correct date.


The datatype used is irrelevant w.r.t. this issue. Unless fully 
qualified with convention used and possibly location on Earth, you have 
no way to say it's local time (requires location), Zulu time, Hebrew 
calendar (requires location), islamic calendar (which version?), fiscal 
(which version/country?), TAI, whatever. There exist a large number of 
conventions and variants for denoting datetime.



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

Re: [sqlite] Subject: Re: SQL Date Import

[Simon Slavin]

On 3 Jun 2018, at 9:48am, Thomas Kurz  wrote:

> he problem not having a DATETIME field is, however, very simple: When reading 
> a foreign database which stores date values as a number, I have to guess on 
> how to get back the correct date. People and companies are very creative in 
> that regard: Is it unix-based? Is it like Excel? Is it days, minutes, 
> seconds, or even milliseconds? Since when? [snip]

You get that from the same place you get the units for numeric units.  Is 
"length" inches or centimetres ?  Is "audit" TRUE when it has been audited or 
when it needs auditing ?

> Import and export from/to text formats shouldn't be a problem either as other 
> DBSs already support DATETIME and TIMESTAMP and obviously have found a 
> representation of such values in SQL (it's probably defined in the SQL 
> standard anyway).

There are indeed data types about date and time in section 4.5 of SQL95.  
They're a weird by modern standards but they are there.

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

Re: [sqlite] Subject: Re: SQL Date Import

[Thomas Kurz]

> One problem with having an actual internal date format is how to dump it into 
> a text file or to a text interface.  You end up turning it into a number or a 
> string anyway, so you might was well store it that way.

The problem not having a DATETIME field is, however, very simple: When reading 
a foreign database which stores date values as a number, I have to guess on how 
to get back the correct date. People and companies are very creative in that 
regard: Is it unix-based? Is it like Excel? Is it days, minutes, seconds, or 
even milliseconds? Since when?

Compatibility shouldn't be affected imho. You can always continue to use 
INTEGER or FLOAT, and databases using INTEGER or FLOAT continue to be fully 
compatible. But newly created databases could store date values in a properly 
defined way that is readable for others as well.

Import and export from/to text formats shouldn't be a problem either as other 
DBSs already support DATETIME and TIMESTAMP and obviously have found a 
representation of such values in SQL (it's probably defined in the SQL standard 
anyway).

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

Re: [sqlite] Subject: Re: SQL Date Import

[Simon Slavin]

On 2 Jun 2018, at 8:55pm, Thomas Kurz  wrote:

> Are there any plans to implement a DATETIME and/or TIMESTAMP field types?

No.  SQLite has many routines which understand standard ways of storing 
datestamps:



So you can store IS0-8601-format strings, unix epoch, or Julian day, and use 
SQLite routines to do things like "get four weeks before the Monday just 
previous to this date" and "get the third Wednesday of the month after this 
date".

One problem with having an actual internal date format is how to dump it into a 
text file or to a text interface.  You end up turning it into a number or a 
string anyway, so you might was well store it that way.

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

Re: [sqlite] Subject: Re: SQL Date Import

[Abroży Nieprzełoży]

> Are there any plans to implement a DATETIME and/or TIMESTAMP field types?

I don't think so. The SQLite team really cares about backward compatibility.

You can store timestamp as a unixepoch integer or as a text in format
supported by date/time functions.
http://www.sqlite.org/draft/lang_datefunc.html


2018-06-02 21:55 GMT+02:00, Thomas Kurz :
> Are there any plans to implement a DATETIME and/or TIMESTAMP field types?
>
> - Original Message -
> From: Simon Slavin 
> To: SQLite mailing list 
> Sent: Saturday, June 2, 2018, 21:04:10
> Subject: [sqlite] Subject: Re:  SQL Date Import
>
> On 2 Jun 2018, at 7:32pm, dmp  wrote:
>
>> By the way, most databases give exactly that INSERT when dumping data
>> for DATE, TIME, TIMESTAMP, etc., text. I'm not advocating a preferred
>> type for storage here.
>
> I think your proposed programme of experimentation is the right way to
> pursue this.  But I wanted to save you some time.
>
> SQLite doesn't have a DATE type.  You can store dates in a SQLite database
> as text, or integers or floating point numbers (e.g. "20180602", a number of
> days, a number of seconds).  But when you ask for a value, that's what
> you'll get back.  Any interpretation of that value as a date is up to you or
> your software.
>
> Simon.
> ___
> 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Subject: Re: SQL Date Import

[Thomas Kurz]

Are there any plans to implement a DATETIME and/or TIMESTAMP field types?

- Original Message - 
From: Simon Slavin 
To: SQLite mailing list 
Sent: Saturday, June 2, 2018, 21:04:10
Subject: [sqlite] Subject: Re:  SQL Date Import

On 2 Jun 2018, at 7:32pm, dmp  wrote:

> By the way, most databases give exactly that INSERT when dumping data
> for DATE, TIME, TIMESTAMP, etc., text. I'm not advocating a preferred
> type for storage here.

I think your proposed programme of experimentation is the right way to pursue 
this.  But I wanted to save you some time.

SQLite doesn't have a DATE type.  You can store dates in a SQLite database as 
text, or integers or floating point numbers (e.g. "20180602", a number of days, 
a number of seconds).  But when you ask for a value, that's what you'll get 
back.  Any interpretation of that value as a date is up to you or your software.

Simon.
___
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] Subject: Re: SQL Date Import

[Simon Slavin]

On 2 Jun 2018, at 7:32pm, dmp  wrote:

> By the way, most databases give exactly that INSERT when dumping data
> for DATE, TIME, TIMESTAMP, etc., text. I'm not advocating a preferred
> type for storage here.

I think your proposed programme of experimentation is the right way to pursue 
this.  But I wanted to save you some time.

SQLite doesn't have a DATE type.  You can store dates in a SQLite database as 
text, or integers or floating point numbers (e.g. "20180602", a number of days, 
a number of seconds).  But when you ask for a value, that's what you'll get 
back.  Any interpretation of that value as a date is up to you or your software.

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

[sqlite] Subject: Re: SQL Date Import

[dmp]

Keith Medcalf wrote:
> Yes, and the database will store the data as entered/bound if it
> cannot be converted to the requested storage type (column affinity).

Yes, that was my understanding and there-lies the problem. A column
of type DATE, TIME, or TIMESTAMP that may have NUMERIC and TEXT data.

> This is VERY IMPORTANT for you to understand fully and completely
> including all the rules for storage class and affinity conversions
> and how they are determined.  I would recommend that you SAY what
> you mean, and not confuse things (including yourself) by using
> "prayerful" data type declarations that are NOT in the proper set
> (INTEGER / REAL / NUMERIC / TEXT / BLOB).

Unfortunately as a middle man between a database designer and a user,
who maybe the table creator, I do not get to decide what they define
and what they put in a table, a combination of types for Date perhaps,
or maybe just TEXT. The example given INSERT could give you the former.

By the way, most databases give exactly that INSERT when dumping data
for DATE, TIME, TIMESTAMP, etc., text. I'm not advocating a preferred
type for storage here.

Thank you for the response. I think I will go ahead and install SQLite
on a machine so that I can experiment some more directly via the command
line, before deciding on an action to take on the issues I'm having.

danap.

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