Re: [sqlite] Date operations when date is a TEXT

[Simon Davies]

On 19 January 2011 10:06, Ian Hardingham <i...@omroth.com> wrote:
> Hey guys.
>
> Probably unwisely, I store dates in the following format:
>
> "year month day hour minute"
>
> For example:
>
> "11 1 4 16 22"
>
> I wish to find all rows in a table which are more than 8 days old.  Is
> there a way of doing this in SQLite or should I just do it in my own code?

You need to construct ISO8601 style date string fro SQLite's date functions...

SQLite version 3.6.11
Enter ".help" for instructions
sqlite>
sqlite>
sqlite> create table tst( id integer primary key, time text );
sqlite>
sqlite> create view tstView1 as select *,
   ...> rtrim( rtrim(
   ...>   rtrim( rtrim(
   ...>     rtrim( rtrim(
   ...>       rtrim( rtrim( time,
   ...>       '1234567890' ), ' ' ),
   ...>     '1234567890' ), ' ' ),
   ...>   '1234567890' ), ' ' ),
   ...> '1234567890' ), ' ' )
   ...>  yr,
   ...> ltrim( ltrim(
   ...>   rtrim( rtrim(
   ...>     rtrim( rtrim(
   ...>       rtrim( rtrim( time,
   ...>       '1234567890' ), ' ' ),
   ...>     '1234567890' ), ' ' ),
   ...>   '1234567890' ), ' ' ),
   ...> '1234567890' ), ' ' )
   ...>  mth,
   ...> ltrim( ltrim(
   ...>   ltrim( ltrim(
   ...>     rtrim( rtrim(
   ...>       rtrim( rtrim( time,
   ...>       '1234567890' ), ' ' ),
   ...>     '1234567890' ), ' ' ),
   ...>   '1234567890' ), ' ' ),
   ...> '1234567890' ), ' ' )
   ...>  day,
   ...> ltrim( ltrim(
   ...>   ltrim( ltrim(
   ...>     ltrim( ltrim(
   ...>       rtrim( rtrim( time,
   ...>       '1234567890' ), ' ' ),
   ...>     '1234567890' ), ' ' ),
   ...>   '1234567890' ), ' ' ),
   ...> '1234567890' ), ' ' )
   ...>  hr,
   ...> ltrim( ltrim(
   ...>   ltrim( ltrim(
   ...>     ltrim( ltrim(
   ...>       ltrim( ltrim( time,
   ...>       '1234567890' ), ' ' ),
   ...>     '1234567890' ), ' ' ),
   ...>   '1234567890' ), ' ' ),
   ...> '1234567890' ), ' ' )
   ...>  min
   ...> from tst;
sqlite>
sqlite> create view tstView2 as
   ...> select *, '20' ||
   ...>   case length( yr ) == 1  when 1 then '0' || yr else yr end || '-' ||
   ...>   case length( mth ) == 1  when 1 then '0' || mth else mth end || '-' ||
   ...>   case length( day ) == 1  when 1 then '0' || day else day end || ' ' ||
   ...>   case length( hr ) == 1  when 1 then '0' || hr else hr end || ':' ||
   ...>   case length( min ) == 1  when 1 then '0' || min else min end
|| ':00' dateStr
   ...> from tstView1;
sqlite>
sqlite> create view tstView3 as
   ...> select *, julianday( dateStr ) julian
   ...> from tstView2;
sqlite>
sqlite>
sqlite> insert into tst( time ) values( '11 1 4 16 22' );
sqlite> insert into tst( time ) values( '1 11 14 6 2' );
sqlite> insert into tst( time ) values( '21 1 24 1 3' );
sqlite> insert into tst( time ) values( '13 3 24 16 22' );
sqlite> insert into tst( time ) values( '10 4 1 16 22' );
sqlite>
sqlite> insert into tst( time ) values( '11 1 11 10 22' );
sqlite> insert into tst( time ) values( '11 1 12 16 22' );
sqlite>
sqlite> select dateStr from tstview3 where julianday( 'now', '-8 days'
) > julian;
2011-01-04 16:22:00
2001-11-14 06:02:00
2010-04-01 16:22:00
2011-01-11 10:22:00
sqlite> select dateStr from tstview3 where julianday( 'now', '-8 days'
) < julian;
2021-01-24 01:03:00
2013-03-24 16:22:00
2011-01-12 16:22:00
sqlite>
sqlite> select datetime( 'now' );
2011-01-19 11:53:40
sqlite>

>
> Thanks,
> Ian

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