Re: [sqlite] how to do select by Date and Time range?

[Stephen Leaf]

On Tuesday 05 July 2005 01:23 pm, jack wu wrote:
> one more question if i may. what should i do if i 'd
> like to have two columns, one for Date, one for Time
> of the day. most of the times, i 'd like to query by
> Date only but i want to display date and time at the
> same time. is julianday('2005-07-01') going to store
> any time information? Thanks.

yes it does.
sqlite> select datetime(julianday('2005-07-01'));
2005-07-01 00:00:00

time is midnight ;)

>
> jack.
>
> --- Stephen Leaf <[EMAIL PROTECTED]> wrote:
> > On Tuesday 05 July 2005 09:53 am, Cory Nelson wrote:
> > > Just an educated guess, but probably because
> >
> > sqlite tries to be as
> >
> > > minimal as possible.  Which I have no complaints
> >
> > with, as comparing a
> >
> > > double will likely be faster than comparing a
> >
> > string.
> > I personally store all mine like this anyway using
> > unix time so I can change
> > the format at anytime. plus it's not only faster to
> > compare programming wise,
> > just compare numbers. no need use functions.
> > strings also are larger than numbers in size wise so
> > you save a few bytes here
> > and there.
> >
> > > On 7/5/05, Johan Danielsson <[EMAIL PROTECTED]>
> >
> > wrote:
> > > > Cory Nelson <[EMAIL PROTECTED]> writes:
> > > > > create table t_foo(bar real);
> > > > > insert into t_foo
> >
> > values(julianday('2005-07-01'));
> >
> > > > Is there any advantage to this compared to
> >
> > storing dates as strings in
> >
> > > > (for instance) ISO8601 format?
> > > >
> > > > /Johan

Re: [sqlite] how to do select by Date and Time range?

[jack wu]

one more question if i may. what should i do if i 'd
like to have two columns, one for Date, one for Time
of the day. most of the times, i 'd like to query by
Date only but i want to display date and time at the
same time. is julianday('2005-07-01') going to store
any time information? Thanks.

jack.


--- Stephen Leaf <[EMAIL PROTECTED]> wrote:

> On Tuesday 05 July 2005 09:53 am, Cory Nelson wrote:
> > Just an educated guess, but probably because
> sqlite tries to be as
> > minimal as possible.  Which I have no complaints
> with, as comparing a
> > double will likely be faster than comparing a
> string.
> I personally store all mine like this anyway using
> unix time so I can change 
> the format at anytime. plus it's not only faster to
> compare programming wise, 
> just compare numbers. no need use functions.
> strings also are larger than numbers in size wise so
> you save a few bytes here 
> and there.
> >
> > On 7/5/05, Johan Danielsson <[EMAIL PROTECTED]>
> wrote:
> > > Cory Nelson <[EMAIL PROTECTED]> writes:
> > > > create table t_foo(bar real);
> > > > insert into t_foo
> values(julianday('2005-07-01'));
> > >
> > > Is there any advantage to this compared to
> storing dates as strings in
> > > (for instance) ISO8601 format?
> > >
> > > /Johan
> 

Re: [sqlite] how to do select by Date and Time range?

[Stephen Leaf]

On Tuesday 05 July 2005 09:53 am, Cory Nelson wrote:
> Just an educated guess, but probably because sqlite tries to be as
> minimal as possible.  Which I have no complaints with, as comparing a
> double will likely be faster than comparing a string.
I personally store all mine like this anyway using unix time so I can change 
the format at anytime. plus it's not only faster to compare programming wise, 
just compare numbers. no need use functions.
strings also are larger than numbers in size wise so you save a few bytes here 
and there.
>
> On 7/5/05, Johan Danielsson <[EMAIL PROTECTED]> wrote:
> > Cory Nelson <[EMAIL PROTECTED]> writes:
> > > create table t_foo(bar real);
> > > insert into t_foo values(julianday('2005-07-01'));
> >
> > Is there any advantage to this compared to storing dates as strings in
> > (for instance) ISO8601 format?
> >
> > /Johan

Re: [sqlite] how to do select by Date and Time range?

[Cory Nelson]

Just an educated guess, but probably because sqlite tries to be as
minimal as possible.  Which I have no complaints with, as comparing a
double will likely be faster than comparing a string.

On 7/5/05, Johan Danielsson <[EMAIL PROTECTED]> wrote:
> Cory Nelson <[EMAIL PROTECTED]> writes:
> 
> > create table t_foo(bar real);
> > insert into t_foo values(julianday('2005-07-01'));
> 
> Is there any advantage to this compared to storing dates as strings in
> (for instance) ISO8601 format?
> 
> /Johan
> 


-- 
Cory Nelson
http://www.int64.org

Re: [sqlite] how to do select by Date and Time range?

[Johan Danielsson]

Cory Nelson <[EMAIL PROTECTED]> writes:

> create table t_foo(bar real);
> insert into t_foo values(julianday('2005-07-01'));

Is there any advantage to this compared to storing dates as strings in
(for instance) ISO8601 format?

/Johan

Re: [sqlite] how to do select by Date and Time range?

[Cory Nelson]

create table t_foo(bar real);
insert into t_foo values(julianday('2005-07-01'));

On 7/4/05, jack wu <[EMAIL PROTECTED]> wrote:
> Cory, thanks for the information. the wiki talks about
> time functions. what should we do when defining the
> table columns? should i just use REAL? can you give a
> sample CREATE table statement? and a sample insert
> statement?
> 
> 
> jack.
> 
> 
> 
> 


-- 
Cory Nelson
http://www.int64.org

Re: [sqlite] how to do select by Date and Time range?

[jack wu]

Cory, thanks for the information. the wiki talks about
time functions. what should we do when defining the
table columns? should i just use REAL? can you give a
sample CREATE table statement? and a sample insert
statement? 


jack.

Re: [sqlite] how to do select by Date and Time range?

[Cory Nelson]

sqlite stores dates/times in floating point, the normal comparison
operators should work fine on them. 
http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions

On 7/4/05, jack wu <[EMAIL PROTECTED]> wrote:
> 
> i have a table which has Date one the columns. i 'd
> like run a query in this form:
> 
> select * from table1 where Date>date1 and Date 
> as we all understand sqlite does not have timestamp
> datatype. i am wondering how everybody is working
> around the limitation. thanks.
> 
> jack.
> 


-- 
Cory Nelson
http://www.int64.org

[sqlite] how to do select by Date and Time range?

[jack wu]

i have a table which has Date one the columns. i 'd
like run a query in this form:

select * from table1 where Date>date1 and Date