On Thu, May 19, 2011 at 7:54 PM, Sandy Bottom <[email protected]> wrote:
> My objective is a query which lists events and calculates the number of
> days
> to the event e.g:
>
>
> Name Days to Event
> --------- -------------
> Christmas 40
>
>
I don't know how to solve your problem in general because different dates
are computed in different ways. How do you compute the date of Easter for
the Eastern Orthodox Chruch, for example? When does Yom Kippur fall this
year? It can be a hard problem.
Christmas is easier. The Christmas day for the current year is:
SELECT strftime('%Y-12-25','now');
The easiest way to compute the number of days difference between two dates
is to subtract the julian day numbers:
SELECT julianday(strftime('%Y-12-25','now')) - julianday(date('now'));
219 is the answer, as I type this. Note however the the calculation is done
for Greenwich. If it is already tomorrow in Greenwich while it is still the
previous day locally (which happens to be the case as I type this - it is
now 00:31 in Greenwich and 20:31 local) then you should add:
SELECT julianday(strftime('%Y-12-25','now','localtime')) -
julianday(date('now','localtime'));
Which comes out to 220.
>
> The event table might look something like:
>
> create table event (
> name varchar (50),
> when text (20));
>
>
> (Regarding storing date values in a text field, I will use whatever
> datatype
> best facilitates the calculation.)
>
>
> What would an 'insert' statement for this table look like? E.g.
>
> insert into event values ('Christmas', ... );
>
>
> What would the select statement which calculates the 'Days to Event' column
> look like? E.g.
>
> select name, ... from event;
>
>
> I expect the answer probably involves using 'now' along with converting a
> string of the form YYYYMMDD to another form, then possibly converting it
> back. To-date my attempts have been unsuccessful.
>
> Hoping someone can help.
>
> Tony
> _______________________________________________
> sqlite-users mailing list
> [email protected]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
--
D. Richard Hipp
[email protected]
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
