Re: [sqlite] Slightly unexpected behaviour when comparing date and datetime

[Nico Williams]

On Tue, Apr 17, 2012 at 2:45 PM, Richard Hipp  wrote:
> On Tue, Apr 17, 2012 at 2:44 PM, Nico Williams wrote:
>> Note that you'll lose any fractional second information when you do
>> this.  On the other hand, fractional second information does not sort
>> properly when compared as text,
>
> Huh?  The standard format is -MM-DD HH:MM:SS.SSS.  I think that sorts
> in time order when compared as strings.  Do you have a counter-example?

I could swear that I observed %f produce variable numbers of digits
for the decimal part.  But now I don't see that.  An Emily Litella
moment for me.

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

Re: [sqlite] Slightly unexpected behaviour when comparing date and datetime

[Richard Hipp]

On Tue, Apr 17, 2012 at 2:44 PM, Nico Williams wrote:

> On Tue, Apr 17, 2012 at 11:18 AM, Mark Jones 
> wrote:
> > I think I'll spend the time going back and storing the dates as integer
> > time (since the epoch) as Nico suggested and just use strftime to convert
> > them as and when required.
>
> Note that you'll lose any fractional second information when you do
> this.  On the other hand, fractional second information does not sort
> properly when compared as text,


Huh?  The standard format is -MM-DD HH:MM:SS.SSS.  I think that sorts
in time order when compared as strings.  Do you have a counter-example?



> so if you need sub-second resolution
> you need to work a little harder.
>
> (Huh, that is strange.  Is there a canonical way to compare timestamps
> with fractional seconds in SQLite3?)
>

The julian day number, stored as a double-precision floating point number
gives you millisecond resolution in the modern era.  Julian day number is
the default and preferred format for dates/times in SQLite.


sqlite> select datetime('now'), julianday('now');
2012-04-17 19:45:11|2456035.32305485





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



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

Re: [sqlite] Slightly unexpected behaviour when comparing date and datetime

[Black, Michael (IS)]

Store them as float or do integer and multiple by a power of 10 to get as many 
digits as you want.



So 1.234 seconds *10^3 can be 1234 integer







Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Nico Williams [n...@cryptonector.com]
Sent: Tuesday, April 17, 2012 1:44 PM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Slightly unexpected behaviour when comparing date 
and datetime

On Tue, Apr 17, 2012 at 11:18 AM, Mark Jones <m...@jonesgroup.co.uk> wrote:
> I think I'll spend the time going back and storing the dates as integer
> time (since the epoch) as Nico suggested and just use strftime to convert
> them as and when required.

Note that you'll lose any fractional second information when you do
this.  On the other hand, fractional second information does not sort
properly when compared as text, so if you need sub-second resolution
you need to work a little harder.

(Huh, that is strange.  Is there a canonical way to compare timestamps
with fractional seconds in SQLite3?)
___
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] Slightly unexpected behaviour when comparing date and datetime

[Nico Williams]

On Tue, Apr 17, 2012 at 11:18 AM, Mark Jones  wrote:
> I think I'll spend the time going back and storing the dates as integer
> time (since the epoch) as Nico suggested and just use strftime to convert
> them as and when required.

Note that you'll lose any fractional second information when you do
this.  On the other hand, fractional second information does not sort
properly when compared as text, so if you need sub-second resolution
you need to work a little harder.

(Huh, that is strange.  Is there a canonical way to compare timestamps
with fractional seconds in SQLite3?)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Slightly unexpected behaviour when comparing date and datetime

[Mark Jones]

Thanks goes out to both of you for your quick responses!

For text in SQLite, delimit with single quotes, not double quotes.  Double
> quotes are used for tricky entity names.  And you probably don't want the
> quotes around the real numbers at all.


I'll go off and re-read up on the quotes and make sure I use them
correctly.

And you're correct, I didn't want the quotes around the real numbers, they
were put in there when I was putting together and testing my sample
database. But not in the real database. *facepalm*

I think I'll spend the time going back and storing the dates as integer
time (since the epoch) as Nico suggested and just use strftime to convert
them as and when required.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Slightly unexpected behaviour when comparing date and datetime

[Nico Williams]

On Tue, Apr 17, 2012 at 10:45 AM, Mark Jones  wrote:
> Afternoon all,
>
> I have the following schema:
>
> CREATE TABLE day
> ("id" INTEGER PRIMARY KEY,
> "timestamp" DATETIME,
> "value" REAL);
>
> And the following sample data:
>
> INSERT INTO day VALUES (NULL, "2012-01-01", "5.0");
> INSERT INTO day VALUES (NULL, "2012-01-02", "6.0");
> INSERT INTO day VALUES (NULL, "2012-01-03", "7.0");
> INSERT INTO day VALUES (NULL, "2012-01-04", "5.0");
>
> When I perform the following query I don't get the expected result:
>
> SELECT * FROM day WHERE timestamp >= '2012-01-01 00:00:00';
> 2|2012-01-02|6.0
> 3|2012-01-03|7.0
> 4|2012-01-04|5.0
>
> Why does this happen? Is this the sort of thing I should bother the SQLite
> develops with (via bug report)?

Because you're doing *string* comparison of strings that represent
date, on the one hand, and date + time on the other.  SQLite3 does not
have a native date/time type, and so has no native date/time
comparison operator either.  You can still do the comparison you want,
but you have to do it slightly differently.  Here's one option:

SELECT * FROM day WHERE datetime(timestamp) >= '2012-01-01 00:00:00';

Another option would be to convert to seconds since the Unix epoch and
then use integer comparison:

SELECT * FROM day WHERE strftime('%s', timestamp) >=
strftime('%s', '2012-01-01 00:00:00');

See http://sqlite.org/lang_datefunc.html

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

Re: [sqlite] Slightly unexpected behaviour when comparing date and datetime

[Simon Slavin]

On 17 Apr 2012, at 4:45pm, Mark Jones  wrote:

> I have the following schema:
> 
> CREATE TABLE day
> ("id" INTEGER PRIMARY KEY,
> "timestamp" DATETIME,
> "value" REAL);

There is no such datatype as 'DATETIME'.  You are actually storing text.  See 
especially section 1.2, but possibly the whole page of



> And the following sample data:
> 
> INSERT INTO day VALUES (NULL, "2012-01-01", "5.0");
> INSERT INTO day VALUES (NULL, "2012-01-02", "6.0");
> INSERT INTO day VALUES (NULL, "2012-01-03", "7.0");
> INSERT INTO day VALUES (NULL, "2012-01-04", "5.0");

For text in SQLite, delimit with single quotes, not double quotes.  Double 
quotes are used for tricky entity names.  And you probably don't want the 
quotes around the real numbers at all.

> When I perform the following query I don't get the expected result:
> 
> SELECT * FROM day WHERE timestamp >= '2012-01-01 00:00:00';
> 2|2012-01-02|6.0
> 3|2012-01-03|7.0
> 4|2012-01-04|5.0

The string '2012-01-01' sorts before the string '2012-01-01 00:00:00'.  Your 
problem is that your strings are being understood and stored as strings, not a 
way of using a string to specify a timestamp.  You could specify times in your 
INSERT commands ...

INSERT INTO day VALUES (NULL, '2012-01-01 00:00:00', 5.0);

or you might choose to store just dates as you already do, and do your SELECT 
using just the date:

SELECT * FROM day WHERE timestamp >= '2012-01-01';

but since you call the field "timestamp" you probably want to figure times in 
it.

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