On 02/09/07, C M <[EMAIL PROTECTED]> wrote:
> Hi, I'm very new to SQLite, and I'm using it with Python.
>
> I want to have queries that will match dates but not care about times.
.
.
.
Hi,
If your dates are stored in julian day (real) format, then the
fractional part will indicate the time of day. This can be removed
with the 'round' function.
Thus:
sqlite> create table tstTbl( a integer primary key, tstDate real );
sqlite>
sqlite> insert into tstTbl( tstDate ) values( julianday('now') );
sqlite> insert into tstTbl( tstDate ) values( julianDay('2007-10-01
12:12:12' ));
sqlite> insert into tstTbl( tstDate ) values( julianDay('2007-11-01
12:13:12' ));
sqlite> insert into tstTbl( tstDate ) values( julianDay('2007-10-01
12:15:12' ));
sqlite> insert into tstTbl( tstDate ) values( julianDay('2007-10-01
12:14:12' ));
sqlite> insert into tstTbl( tstDate ) values( julianDay('2007-10-01
12:14:13' ));
sqlite> insert into tstTbl( tstDate ) values( julianDay('2007-10-01
11:14:13' ));
sqlite> insert into tstTbl( tstDate ) values( julianDay('2007-10-01
13:14:13' ));
sqlite> insert into tstTbl( tstDate ) values( julianDay('2007-10-01
14:14:13' ));
sqlite>
sqlite> select *, dateTime( tstDate ) from tstTbl order by tstDate;
1|2454345.90499024|2007-09-02 09:43:11
7|2454374.96820602|2007-10-01 11:14:13
2|2454375.00847222|2007-10-01 12:12:12
5|2454375.00986111|2007-10-01 12:14:12
6|2454375.00987269|2007-10-01 12:14:13
4|2454375.01055556|2007-10-01 12:15:12
8|2454375.05153935|2007-10-01 13:14:13
9|2454375.09320602|2007-10-01 14:14:13
3|2454406.00916667|2007-11-01 12:13:12
sqlite>
sqlite> select *, dateTime( tstDate ) from tstTbl where
...> cast(round(tstDate) as integer)=
...> cast(round(julianday('2007-11-01')) as integer);
3|2454406.00916667|2007-11-01 12:13:12
sqlite>
sqlite> select *, dateTime( tstDate ) from tstTbl where
...> cast(round(tstDate) as integer)=
...> cast(round(julianday('2007-10-01')) as integer);
2|2454375.00847222|2007-10-01 12:12:12
4|2454375.01055556|2007-10-01 12:15:12
5|2454375.00986111|2007-10-01 12:14:12
6|2454375.00987269|2007-10-01 12:14:13
7|2454374.96820602|2007-10-01 11:14:13
8|2454375.05153935|2007-10-01 13:14:13
9|2454375.09320602|2007-10-01 14:14:13
sqlite>
sqlite> select *, dateTime( tstDate ) from tstTbl where
...> cast(round(tstDate) as integer)=
...> cast(round(julianday('2007-09-02')) as integer);
1|2454345.90499024|2007-09-02 09:43:11
sqlite>
Rgds,
Simon
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
