Re: [sqlite] Late data typing. Am I missing something?

[John Crenshaw]

> Um...how do I go to the page that describes the date-time information
> without just 'knowing' it's there?  For example, there seems to be no
> path to http://www.sqlite.org/lang_datefunc.html from
> http://.sqlite.org.

I used Google personally. "sqlite date functions"

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

Re: [sqlite] Late data typing. Am I missing something?

[Ted Rolle]

On Thu, 29 Oct 2009 17:25:34 -0400
Pavel Ivanov  wrote:

> > For example, there seems to be no
> > path to http://www.sqlite.org/lang_datefunc.html from
> > http://.sqlite.org.
> 
> Go to Documentation -> SQL Syntax -> date and time functions.
> 
> Pavel
> 
Uh...RTFM?  Thanks!

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

Re: [sqlite] Late data typing. Am I missing something?

[Jay A. Kreibich]

On Thu, Oct 29, 2009 at 05:21:29PM -0400, Ted Rolle scratched on the wall:
> I'm learning so much from this discussion!
> I've come to believe that late (lazy) typing is a Good Thing(tm).
> 
> Um...how do I go to the page that describes the date-time information
> without just 'knowing' it's there?  For example, there seems to be no
> path to http://www.sqlite.org/lang_datefunc.html from
> http://.sqlite.org.

  More or less how you would expect to get there:

  SQLite Home
  http://sqlite.org/

  SQL Language
  http://sqlite.org/lang.html

  date and time functions (mixed in with SQL commands)
  http://sqlite.org/lang_datefunc.html


   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Late data typing. Am I missing something?

[Pavel Ivanov]

> For example, there seems to be no
> path to http://www.sqlite.org/lang_datefunc.html from
> http://.sqlite.org.

Go to Documentation -> SQL Syntax -> date and time functions.

Pavel

On Thu, Oct 29, 2009 at 5:21 PM, Ted Rolle  wrote:
> I'm learning so much from this discussion!
> I've come to believe that late (lazy) typing is a Good Thing(tm).
>
> Um...how do I go to the page that describes the date-time information
> without just 'knowing' it's there?  For example, there seems to be no
> path to http://www.sqlite.org/lang_datefunc.html from
> http://.sqlite.org.
>
> Any help appreciated!
>
> Ted
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Late data typing. Am I missing something?

[Ted Rolle]

I'm learning so much from this discussion!
I've come to believe that late (lazy) typing is a Good Thing(tm).

Um...how do I go to the page that describes the date-time information
without just 'knowing' it's there?  For example, there seems to be no
path to http://www.sqlite.org/lang_datefunc.html from
http://.sqlite.org.

Any help appreciated!

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

Re: [sqlite] Late data typing. Am I missing something?

[Jay A. Kreibich]

On Thu, Oct 29, 2009 at 03:21:13PM -0500, Nicolas Williams scratched on the 
wall:
> On Wed, Oct 28, 2009 at 07:11:29PM +, O'Neill, Owen wrote:

> > I must agree with other posters that the lack of an exposed timestamp
> > type does feel like something of a gap.
> 
> Given the rather large number of reasonable ways to represent
> timestamps, I have to disagree.  I'd rather have the freedom to use
> whichever representation is best for my needs.

  I have to agree that more rigid database enforcement is not the
  solution.

  The question is a lot more complex than "we need a datetime
  datatype!".  It isn't that simple.  Most RDBMS products define a
  half-dozen or more native date/time datatypes.  You usually have a
  date (day in time), a time (without a date) a datetime (point in
  time), a date-duration (interval of days), and a time-duration.
  Sometimes you also have stuff like a yearless date (e.g. a day of the
  year).  Then there are the other odd ones, like epoch values.
  
  Then you have all those datatypes over again with or without
  additional timezone information. 
  
  And then you have all kinds of messed up rules about how, if you
  have a datetime and want to add six hours, you need to add a
  time-duration, not a "time", and the result is another datetime.
  Or add a date and time to get a datetime.  
  
  And you need a completely different system if you need to support
  an older calendar.  If you think three-value-logic is confusing,
  just wait!  And these rules (and how strict they are) is important...
  after all, the desire for stronger typing is what started all this.

> What's needed is a _cheap_ function that can be used in a CHECK
> constraint on timestamp columns.  And, perhaps, a common convention for
> naming types that correspond to specific timestamp representations.

  I think this is the right path.  Rather than worrying about doubling
  the number of native datatypes in SQLite, it would be nice to see a
  clean, powerful, and well documented library of SQL functions that 
  did all these conversions, verifications, and calculations.

  The existing functions do fairly well.  They may be a bit expensive,
  but they tend to get the job done-- if you know how to use them
  correctly.  This is where a number of people stand up and say the
  documentation is poor, which may be partly correct, but I think the
  real issue here is that dates, times, and timezones are hard.  They
  sound very simple, and we all use them every day, so it feels like it
  just shouldn't be hard or complex.  But it is.  And making more and
  more datatypes won't fix that.

   -j
 
-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Late data typing. Am I missing something?

[John Crenshaw]

> > I must agree with other posters that the lack of an exposed
timestamp
> > type does feel like something of a gap.
> 
> Given the rather large number of reasonable ways to represent
> timestamps, I have to disagree.  I'd rather have the freedom to use
> whichever representation is best for my needs.
> 
> E.g., if a table contains rows representing Unix files, then I'll
> probably want to store seconds since the Unix epoch because that will
> mean fewer conversions, depending on how I use that table anyways.

An internal timestamp type wouldn't force you to use it. Remember you
still have dynamic typing. You could choose to store an integer instead
and that would be fine. The point is that it would be good to have an
internal type specifically for storing time, just like there are types
for real numbers, integers, blobs, and text. Time could be stored as it
is now (a real number) with perhaps a timezone, but would have a
distinct type identifying it. I expect the reason this isn't done is
that at the SQL level there is no wonderful way to recognize a constant
as a "time". The data type would mostly only be relevant when binding,
where the strong typing of the API clarifies things.

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

Re: [sqlite] Late data typing. Am I missing something?

[Nicolas Williams]

On Wed, Oct 28, 2009 at 07:11:29PM +, O'Neill, Owen wrote:
> You can get close if you put some check constraints on the columns.

This is key: you can get the benefits of static and dynamic data typing.

> I must agree with other posters that the lack of an exposed timestamp
> type does feel like something of a gap.

Given the rather large number of reasonable ways to represent
timestamps, I have to disagree.  I'd rather have the freedom to use
whichever representation is best for my needs.

E.g., if a table contains rows representing Unix files, then I'll
probably want to store seconds since the Unix epoch because that will
mean fewer conversions, depending on how I use that table anyways.

What's needed is a _cheap_ function that can be used in a CHECK
constraint on timestamp columns.  And, perhaps, a common convention for
naming types that correspond to specific timestamp representations.

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

Re: [sqlite] Late data typing. Am I missing something?

[Jens Miltner]

Am 29.10.2009 um 14:31 schrieb Simon Slavin:

>
> On 29 Oct 2009, at 9:36am, John Crenshaw wrote:
>
>>> Consider the case of an application using an SQLite database to  
>>> store
>>> its settings (like the Windows registry, but portable).  The dynamic
>>> type system is great for this.
>>>
>>> CREATE TABLE Settings (Name TEXT PRIMARY KEY, Value BLOB);
>>>
>>> NameValue
>>> -   ---
>>> LogPath 'E:\log\FooApp'
>>> MaxAttachmentSize   250
>>> LastUpdate  2455130.1125
>>>
>>> Now, in the SQLite equivalent of regedit, how is it supposed to know
>>> that LastUpdate is timestamp 2009-10-25 14:42:00 but
>>> MaxAttachmentSize
>>> is NOT the date 2132-08-31 12:00:00?  Without knowledge of the
>>> application that created this table, it can't.
>>
>> A system like this would need a type column as well. Storing dates as
>> text doesn't change that, because at some level you'll still need to
>> distinguish between regular text, and a date stored as text. Once you
>> add a type column, it is no longer ambiguous.
>
> Erm ... no.  You just call 'typeof()' on the value that was returned.
> That tells you what type of thing it is.

Only sort of...

sqlite> SELECT typeof(julianday());
real

So it will tell you which of the _internal_ types it is, but since  
SQLite lacks an internal type for date/time, you're back to where the  
discussion started...
You'll still need to have some sort of context for the column value  
that tells you what higher-level data type it represents.



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

Re: [sqlite] Late data typing. Am I missing something?

[Simon Slavin]

On 29 Oct 2009, at 9:36am, John Crenshaw wrote:

>> Consider the case of an application using an SQLite database to store
>> its settings (like the Windows registry, but portable).  The dynamic
>> type system is great for this.
>>
>> CREATE TABLE Settings (Name TEXT PRIMARY KEY, Value BLOB);
>>
>> NameValue
>> -   ---
>> LogPath 'E:\log\FooApp'
>> MaxAttachmentSize   250
>> LastUpdate  2455130.1125
>>
>> Now, in the SQLite equivalent of regedit, how is it supposed to know
>> that LastUpdate is timestamp 2009-10-25 14:42:00 but  
>> MaxAttachmentSize
>> is NOT the date 2132-08-31 12:00:00?  Without knowledge of the
>> application that created this table, it can't.
>
> A system like this would need a type column as well. Storing dates as
> text doesn't change that, because at some level you'll still need to
> distinguish between regular text, and a date stored as text. Once you
> add a type column, it is no longer ambiguous.

Erm ... no.  You just call 'typeof()' on the value that was returned.   
That tells you what type of thing it is.

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

Re: [sqlite] Late data typing. Am I missing something?

[John Crenshaw]

>>> *if* you know that the number *is* a date.
>>> 
>>
>> If the column has a type of timestamp, it should be safe to always
>> assume that it IS a date.
>sqlite> CREATE TABLE t1 (StartTime TIMESTAMP, Duration REAL);
>sqlite> CREATE TABLE t2 AS SELECT StartTime, StartTime + Duration / 
>86400.0 AS StopTime FROM t1;
>sqlite> SELECT sql FROM sqlite_master WHERE name = 't2';
>CREATE TABLE t2(StartTime TIMESTAMP,StopTime)
>
>What column type?

Still a timestamp (assuming that you stored the dates as Julian values
and that the math was therefore valid.) SQLite doesn't care what the
column type is listed as so it doesn't matter what sqlite_master says.
All that matters is how you used the data, and in this case the
calculated value is still a timestamp.

>> Don't put non-dates in it, and there isn't a
>> problem. I can't imagine a case where you would not know whether the
>> value is a date. Am I missing something here?
>>   
>Consider the case of an application using an SQLite database to store 
>its settings (like the Windows registry, but portable).  The dynamic 
>type system is great for this.
>
>CREATE TABLE Settings (Name TEXT PRIMARY KEY, Value BLOB);
>
>NameValue
>-   ---
>LogPath 'E:\log\FooApp'
>MaxAttachmentSize   250
>LastUpdate  2455130.1125
>
>Now, in the SQLite equivalent of regedit, how is it supposed to know 
>that LastUpdate is timestamp 2009-10-25 14:42:00 but MaxAttachmentSize 
>is NOT the date 2132-08-31 12:00:00?  Without knowledge of the 
>application that created this table, it can't.

A system like this would need a type column as well. Storing dates as
text doesn't change that, because at some level you'll still need to
distinguish between regular text, and a date stored as text. Once you
add a type column, it is no longer ambiguous.

Dates don't have a special data type internally so you have to choose to
treat them as dates for them to be dates. You can store them as strings
and accept the performance hit that will come with that, or you can
store them as Julian dates, and accept a little extra typing when you
look at your data in a generic viewer. Either way though, it's only a
date if you treat it like one.

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

Re: [sqlite] Late data typing. Am I missing something?

[Dan Bishop]

John Crenshaw wrote:
>> *if* you know that the number *is* a date.
>> 
>
> If the column has a type of timestamp, it should be safe to always
> assume that it IS a date.
sqlite> CREATE TABLE t1 (StartTime TIMESTAMP, Duration REAL);
sqlite> CREATE TABLE t2 AS SELECT StartTime, StartTime + Duration / 
86400.0 AS StopTime FROM t1;
sqlite> SELECT sql FROM sqlite_master WHERE name = 't2';
CREATE TABLE t2(StartTime TIMESTAMP,StopTime)

What column type?
> Don't put non-dates in it, and there isn't a
> problem. I can't imagine a case where you would not know whether the
> value is a date. Am I missing something here?
>   
Consider the case of an application using an SQLite database to store 
its settings (like the Windows registry, but portable).  The dynamic 
type system is great for this.

CREATE TABLE Settings (Name TEXT PRIMARY KEY, Value BLOB);

NameValue
-   ---
LogPath 'E:\log\FooApp'
MaxAttachmentSize   250
LastUpdate  2455130.1125

Now, in the SQLite equivalent of regedit, how is it supposed to know 
that LastUpdate is timestamp 2009-10-25 14:42:00 but MaxAttachmentSize 
is NOT the date 2132-08-31 12:00:00?  Without knowledge of the 
application that created this table, it can't.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Late data typing. Am I missing something?

[John Crenshaw]

> *if* you know that the number *is* a date.

If the column has a type of timestamp, it should be safe to always
assume that it IS a date. Don't put non-dates in it, and there isn't a
problem. I can't imagine a case where you would not know whether the
value is a date. Am I missing something here?

The datetime() function will accept Julian or string representations
(including special strings like 'now') so even if you did something
really nasty like store dates sometimes in Julian format and sometimes
as strings, the date and time functions will STILL get things right,
though sorting would be a mess and indexes would be useless.

> This is fine as long as you always view your data with 
> application-specific tools and never with generic ones.

Julian *is* the preferred internal format for dates in SQLite. There is
nothing application specific about this. I actually tested the queries
in a generic viewer. I used SELECT Julian('now'); in sqlite3Explorer to
get the "2455133.71759947" value used in my original example.

> it's still worth mentioning that [timezones] can be
> part of the parameter.
You are right of course. They should have been mentioned in the
documentation.

John

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Dan Bishop
Sent: Thursday, October 29, 2009 3:15 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Late data typing. Am I missing something?

John Crenshaw wrote:
> Strings have a number of other disadvantages in this case. They take
> more computations to compare, they take time to parse when you read
> them, and they take longer to build when you insert them. Generally,
> storing dates as a number of some sort is ideal.
>   
I do agree with that.  The problem is that the ideal way to store a date

is different from the ideal way to *display* a date.  And that the 
conversion between the two has to be done manually.
> Building a query to return the value as a human readable string is
> fairly easy:
> SELECT datetime(2455133.71759947); -- returns '2009-10-29 05:13:20'
>   
Yes, it's easy to do that -- *if* you know that the number *is* a date.

This is fine as long as you always view your data with 
application-specific tools and never with generic ones.
> I imagine the timezones aren't documented, because they aren't
actually
> stored if the Julian format is used internally (they have to be
> converted to get the Julian in UTC.) If you use a string, it can store
> the timezone I guess, but it will cost you in terms of speed.
That's not the point.  Timezones can't be part of the return value of 
the strftime/datetime/julianday functions, but it's still worth 
mentioning that they can be part of the parameter.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Late data typing. Am I missing something?

[Dan Bishop]

John Crenshaw wrote:
> Strings have a number of other disadvantages in this case. They take
> more computations to compare, they take time to parse when you read
> them, and they take longer to build when you insert them. Generally,
> storing dates as a number of some sort is ideal.
>   
I do agree with that.  The problem is that the ideal way to store a date 
is different from the ideal way to *display* a date.  And that the 
conversion between the two has to be done manually.
> Building a query to return the value as a human readable string is
> fairly easy:
> SELECT datetime(2455133.71759947); -- returns '2009-10-29 05:13:20'
>   
Yes, it's easy to do that -- *if* you know that the number *is* a date.  
This is fine as long as you always view your data with 
application-specific tools and never with generic ones.
> I imagine the timezones aren't documented, because they aren't actually
> stored if the Julian format is used internally (they have to be
> converted to get the Julian in UTC.) If you use a string, it can store
> the timezone I guess, but it will cost you in terms of speed.
That's not the point.  Timezones can't be part of the return value of 
the strftime/datetime/julianday functions, but it's still worth 
mentioning that they can be part of the parameter.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Late data typing. Am I missing something?

[John Crenshaw]

Strings have a number of other disadvantages in this case. They take
more computations to compare, they take time to parse when you read
them, and they take longer to build when you insert them. Generally,
storing dates as a number of some sort is ideal.

Building a query to return the value as a human readable string is
fairly easy:
SELECT datetime(2455133.71759947); -- returns '2009-10-29 05:13:20'

I imagine the timezones aren't documented, because they aren't actually
stored if the Julian format is used internally (they have to be
converted to get the Julian in UTC.) If you use a string, it can store
the timezone I guess, but it will cost you in terms of speed.

John
 
-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Dan Bishop
Sent: Thursday, October 29, 2009 12:26 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Late data typing. Am I missing something?

John Crenshaw wrote:
> SQLite has plenty of date editing routines. Dates are stored in a
double
> as a Julian date.
Well, that's one way of doing it.  I store them as strings because I 
wanted a human-readable format.  The downside is that this requires 19 
bytes instead of 8.  I wish SQLite could handle the storage optimization

behind the scenes.
> SQLite's understanding of "dates" is capable of
> supporting null, date, time, or datetime. The only real problem is
that
> timezone is not stored, dates are always stored and retrieved in UTC,
> and dates with timezones are converted prior to storage.
Wow!  I didn't realize that SQLite supported timezones, but sure enough,

it does:

sqlite> select datetime('2009-10-28T22:54:52-05:00');
2009-10-29 03:54:52

Why isn't this documented at http://www.sqlite.org/lang_datefunc.html ?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Late data typing. Am I missing something?

[Dan Bishop]

Jay A. Kreibich wrote:
>> -Original Message-
>> From: sqlite-users-boun...@sqlite.org
>> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of O'Neill, Owen
>> Sent: Wednesday, October 28, 2009 3:11 PM
>> To: General Discussion of SQLite Database
>> Subject: Re: [sqlite] Late data typing. Am I missing something?
>>
>> You can get close if you put some check constraints on the columns.
>> 
>
>
> On Wed, Oct 28, 2009 at 03:24:34PM -0400, John Crenshaw scratched on the wall:
>   
>> Yeah, the code is fortunately all there, so once you know what you're
>> looking for it is easy to copy out, but it should have been exposed in
>> the API.
>> 
>
>   No, no... Something like this, in SQL:
>
> CREATE TABLE t ( i INTEGER CHECK (typeof(i) = 'integer') );
>
>   This allows "strong" type checking on just the columns you care
>   about.  Just be aware that it also prevents NULLs.
>
>   
What's stopping you from adding "OR i IS NULL" to the CHECK constraint?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Late data typing. Am I missing something?

[Dan Bishop]

John Crenshaw wrote:
> SQLite has plenty of date editing routines. Dates are stored in a double
> as a Julian date.
Well, that's one way of doing it.  I store them as strings because I 
wanted a human-readable format.  The downside is that this requires 19 
bytes instead of 8.  I wish SQLite could handle the storage optimization 
behind the scenes.
> SQLite's understanding of "dates" is capable of
> supporting null, date, time, or datetime. The only real problem is that
> timezone is not stored, dates are always stored and retrieved in UTC,
> and dates with timezones are converted prior to storage.
Wow!  I didn't realize that SQLite supported timezones, but sure enough, 
it does:

sqlite> select datetime('2009-10-28T22:54:52-05:00');
2009-10-29 03:54:52

Why isn't this documented at http://www.sqlite.org/lang_datefunc.html ?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Late data typing. Am I missing something?

[Jay A. Kreibich]

> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of O'Neill, Owen
> Sent: Wednesday, October 28, 2009 3:11 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Late data typing. Am I missing something?
> 
> You can get close if you put some check constraints on the columns.


On Wed, Oct 28, 2009 at 03:24:34PM -0400, John Crenshaw scratched on the wall:
> Yeah, the code is fortunately all there, so once you know what you're
> looking for it is easy to copy out, but it should have been exposed in
> the API.

  No, no... Something like this, in SQL:

CREATE TABLE t ( i INTEGER CHECK (typeof(i) = 'integer') );

  This allows "strong" type checking on just the columns you care
  about.  Just be aware that it also prevents NULLs.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Late data typing. Am I missing something?

[John Crenshaw]

Yeah, the code is fortunately all there, so once you know what you're
looking for it is easy to copy out, but it should have been exposed in
the API.

John

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of O'Neill, Owen
Sent: Wednesday, October 28, 2009 3:11 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Late data typing. Am I missing something?


You can get close if you put some check constraints on the columns.

I must agree with other posters that the lack of an exposed timestamp
type does feel like something of a gap.

Owen

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Ted Rolle
Sent: Wednesday, October 28, 2009 5:57 PM
To: sqlite-users
Subject: [sqlite] Late data typing. Am I missing something?

Doesn't dynamic data typing lead to bad data?
And proliferation of home-grown editing routines?
It seems that a strict data typing at column definition time would be
MUCH better.  For instance, date-editing routines...

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

Re: [sqlite] Late data typing. Am I missing something?

[O'Neill, Owen]

You can get close if you put some check constraints on the columns.

I must agree with other posters that the lack of an exposed timestamp
type does feel like something of a gap.

Owen

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Ted Rolle
Sent: Wednesday, October 28, 2009 5:57 PM
To: sqlite-users
Subject: [sqlite] Late data typing. Am I missing something?

Doesn't dynamic data typing lead to bad data?
And proliferation of home-grown editing routines?
It seems that a strict data typing at column definition time would be
MUCH better.  For instance, date-editing routines...

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

Re: [sqlite] Late data typing. Am I missing something?

[John Crenshaw]

SQLite's data typing means it can support any and all field types
supported in any other SQL database. That's a big deal. For the most
part, the proper method for accessing any given data is going to be
simple and universal. Homegrown routines will only happen if people have
specific homegrown needs.

I'm a huge fan of strong typing, but I enforce that at the application
level with wrapper classes for each table. I don't see a particular
problem with SQLite's weak typing internally.

SQLite has plenty of date editing routines. Dates are stored in a double
as a Julian date. Check out the SQLite code to see how this was done.
SQLite doesn't expose the date structure and routines at the C level (a
major oversight IMO), so you'll need to lift the dates code and put it
in a new public class. SQLite's understanding of "dates" is capable of
supporting null, date, time, or datetime. The only real problem is that
timezone is not stored, dates are always stored and retrieved in UTC,
and dates with timezones are converted prior to storage. If you need to
retain timezone you could replace all the date functions with your own
version, and store the dates differently, or you could store the
timezone separately.

John

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Ted Rolle
Sent: Wednesday, October 28, 2009 1:57 PM
To: sqlite-users
Subject: [sqlite] Late data typing. Am I missing something?

Doesn't dynamic data typing lead to bad data?
And proliferation of home-grown editing routines?
It seems that a strict data typing at column definition time would be
MUCH better.  For instance, date-editing routines...

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

Re: [sqlite] Late data typing. Am I missing something?

[Simon Slavin]

On 28 Oct 2009, at 5:57pm, Ted Rolle wrote:

> Doesn't dynamic data typing lead to bad data?
> And proliferation of home-grown editing routines?

True in an application which interacts with a user.  Not true in a  
database backend.  SQLite does not at any time interact with a user:  
it does not accept data, so it doesn't have to validate it.  Your  
application is free to validate data according to whatever you feel  
'valid' means.

> It seems that a strict data typing at column definition time would be
> MUCH better.  For instance, date-editing routines...

SQLite does not have a 'date' type.

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

Re: [sqlite] Late data typing. Am I missing something?

[Igor Tandetnik]

Ted Rolle  wrote:
> Doesn't dynamic data typing lead to bad data?

No. Buggy programs lead to bad data.

> It seems that a strict data typing at column definition time would be
> MUCH better.  For instance, date-editing routines...

There is no shortage of database systems featuring strict typing. Please feel 
free to choose one.

Igor Tandetnik

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

[sqlite] Late data typing. Am I missing something?

[Ted Rolle]

Doesn't dynamic data typing lead to bad data?
And proliferation of home-grown editing routines?
It seems that a strict data typing at column definition time would be
MUCH better.  For instance, date-editing routines...

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