Subject: Re: SQL Date Import

classic Classic list List threaded Threaded
10 messages Options
dmp
Reply | Threaded
Open this post in threaded view
|

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Subject: Re: SQL Date Import

Simon Slavin-3
On 2 Jun 2018, at 7:32pm, dmp <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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 <[hidden email]>
To: SQLite mailing list <[hidden email]>
Sent: Saturday, June 2, 2018, 21:04:10
Subject: [sqlite] Subject: Re:  SQL Date Import

On 2 Jun 2018, at 7:32pm, dmp <[hidden email]> 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
[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: 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 <[hidden email]>:

> Are there any plans to implement a DATETIME and/or TIMESTAMP field types?
>
> ----- Original Message -----
> From: Simon Slavin <[hidden email]>
> To: SQLite mailing list <[hidden email]>
> Sent: Saturday, June 2, 2018, 21:04:10
> Subject: [sqlite] Subject: Re:  SQL Date Import
>
> On 2 Jun 2018, at 7:32pm, dmp <[hidden email]> 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
> [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
>
_______________________________________________
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: Subject: Re: SQL Date Import

Simon Slavin-3
In reply to this post by Thomas Kurz
On 2 Jun 2018, at 8:55pm, Thomas Kurz <[hidden email]> 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:

<https://sqlite.org/lang_datefunc.html>

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Subject: Re: SQL Date Import

Simon Slavin-3
On 3 Jun 2018, at 9:48am, Thomas Kurz <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Hick Gunter
In reply to this post by Thomas Kurz
Not even Microsoft Excel has a dedicated datetime/timestamp type. It is just a presentation layer attribute of a floating point value. Also, you get to choose the way you want calendar data to be stored. So why?

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Thomas Kurz
Gesendet: Samstag, 02. Juni 2018 21:56
An: SQLite mailing list <[hidden email]>
Betreff: [EXTERNAL] Re: [sqlite] Subject: Re: SQL Date Import

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

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

On 2 Jun 2018, at 7:32pm, dmp <[hidden email]> 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
[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


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
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: Subject: Re: SQL Date Import

Jean-Christophe Deschamps-3
In reply to this post by Thomas Kurz

>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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
dmp
Reply | Threaded
Open this post in threaded view
|

Re: Subject: Re: SQL Date Import

dmp
In reply to this post by 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, YYYY-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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users