On 21 Apr 2010, at 9:36pm, Adam DeVita wrote:
> /*then */
> update date_dimension set julian_day = julianday('now') -
> julianday('1990-01-01') + epoch_day;
>
> /* then uh.... some query that updates the table containing the julian
> date of every day from Jan 1, 1990 through 2030, and fill in the month
> name, year, quarter in nice user friendly strings.*/
>
> Is this approach better than generating a list of date strings for all
> possible dates, throwing away the Feb 29s from non leap years, and then
> parsing the string to get ye year, month, day, day of year, age from epoch,
> month name, etc?
You don't have to do all that. You can use the existing SQLite functions to
generate all possible dates for you. Taking a look at the Examples section on
this page
http://www.sqlite.org/lang_datefunc.html
something like the following
SELECT date(julianday('1990-01-01')','+0 days')
will give you one day after the start, and you should be able to generate
further days by composing further strings like '+1 days'. Another way to do it
is to know that 1 day = 86400 seconds, and just continually add that number for
each next day.
Of course, these functions return a number, and it's the numbers which will be
stored in your database, presumably in a column with REAL affinity. You might
instead prefer to process the numbers you get, producing and storing a string
like '19900101' which means you'll be able to read your dates with the naked
eye and understand them without processing.
Simon.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
