Selecting dates...

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

Selecting dates...

jose isaias cabrera

Greetings!

I know that SQLite dates are of the form YYYY-MM-DD and I like that. :-)  I
want to find out why these are working.

create table t (a date, val integer);
insert into t values ('2010-01-01', 10);
insert into t values ('2010-1-1', 10);
insert into t values ('2010-1-01', 10);
insert into t values ('2010-02-01', 10);
insert into t values ('2010-2-01', 10);

select sum(val) from t where a BETWEEN '2010-01-01' AND '2010-01-31';
select sum(val) from t where a BETWEEN '2010-01-01' AND '2010-02-01';
select sum(val) from t where a BETWEEN '2010-01-01' AND '2010-12-31';

sqlite> select sum(val) from t where a BETWEEN '2010-01-01' AND
'2010-01-31';
10
sqlite> select sum(val) from t where a BETWEEN '2010-01-01' AND
'2010-02-01';
20
sqlite> select sum(val) from t where a BETWEEN '2010-01-01' AND
'2010-12-31';
40

Thoughts?  Thanks.

josé

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Selecting dates...

Petite Abeille-2

On Sep 2, 2014, at 9:48 PM, jose isaias cabrera <[hidden email]> wrote:

> Thoughts?  Thanks.

SQLite doesn’t have date per se. You are free to store dates as either text or number, or anything you please. But it’s your responsibility to keep it straight.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Selecting dates...

Richard Hipp-3
In reply to this post by jose isaias cabrera
On Tue, Sep 2, 2014 at 3:48 PM, jose isaias cabrera <[hidden email]
> wrote:

>
> Greetings!
>
> I know that SQLite dates are of the form YYYY-MM-DD and I like that. :-)
> I want to find out why these are working.
>

SQLite does not have a special "date" type.  SQLite stores dates as either
strings, or integers, or floating point numbers.  In your case it is
storing and comparing them as strings.


>
> create table t (a date, val integer);
>

If you instead said:

     create table t(a TEXT, val integer);

Would you then understand how it works?  If so, then my explanation is that
it works *exactly* the same why when you substitute "date" for "text" in
the table declaration.



> insert into t values ('2010-01-01', 10);
> insert into t values ('2010-1-1', 10);
> insert into t values ('2010-1-01', 10);
> insert into t values ('2010-02-01', 10);
> insert into t values ('2010-2-01', 10);
>
> select sum(val) from t where a BETWEEN '2010-01-01' AND '2010-01-31';
> select sum(val) from t where a BETWEEN '2010-01-01' AND '2010-02-01';
> select sum(val) from t where a BETWEEN '2010-01-01' AND '2010-12-31';
>
> sqlite> select sum(val) from t where a BETWEEN '2010-01-01' AND
> '2010-01-31';
> 10
> sqlite> select sum(val) from t where a BETWEEN '2010-01-01' AND
> '2010-02-01';
> 20
> sqlite> select sum(val) from t where a BETWEEN '2010-01-01' AND
> '2010-12-31';
> 40
>
> Thoughts?  Thanks.
>
> josé
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Selecting dates...

jose isaias cabrera

"Richard Hipp" wrote...

> On Tue, Sep 2, 2014 at 3:48 PM, jose isaias cabrera
> <[hidden email]
>> wrote:
>
>>
>> Greetings!
>>
>> I know that SQLite dates are of the form YYYY-MM-DD and I like that. :-)
>> I want to find out why these are working.
>>
>
> SQLite does not have a special "date" type.  SQLite stores dates as either
> strings, or integers, or floating point numbers.  In your case it is
> storing and comparing them as strings.
>
>
>>
>> create table t (a date, val integer);
>>
>
> If you instead said:
>
>     create table t(a TEXT, val integer);
>
> Would you then understand how it works?  If so, then my explanation is
> that
> it works *exactly* the same why when you substitute "date" for "text" in
> the table declaration.

Thanks.

>
>
>
>> insert into t values ('2010-01-01', 10);
>> insert into t values ('2010-1-1', 10);
>> insert into t values ('2010-1-01', 10);
>> insert into t values ('2010-02-01', 10);
>> insert into t values ('2010-2-01', 10);
>>
>> select sum(val) from t where a BETWEEN '2010-01-01' AND '2010-01-31';
>> select sum(val) from t where a BETWEEN '2010-01-01' AND '2010-02-01';
>> select sum(val) from t where a BETWEEN '2010-01-01' AND '2010-12-31';
>>
>> sqlite> select sum(val) from t where a BETWEEN '2010-01-01' AND
>> '2010-01-31';
>> 10
>> sqlite> select sum(val) from t where a BETWEEN '2010-01-01' AND
>> '2010-02-01';
>> 20
>> sqlite> select sum(val) from t where a BETWEEN '2010-01-01' AND
>> '2010-12-31';
>> 40
>>
>> Thoughts?  Thanks.
>>
>> josé
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
>
> --
> D. Richard Hipp
> [hidden email]
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Selecting dates...

jose isaias cabrera
In reply to this post by Petite Abeille-2
"Petite Abeille" wrote...
>
> On Sep 2, 2014, at 9:48 PM, jose isaias cabrera <[hidden email]>
> wrote:
>
>> Thoughts?  Thanks.
>
> SQLite doesn’t have date per se. You are free to store dates as either
> text or number, or anything you please. But it’s your responsibility to
> keep it straight.

thanks.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users