[SGVLUG] Can SQL even do this?

John E. Kreznar jek at ininx.com
Tue Oct 28 20:30:15 PDT 2008

Hash: SHA1

"Emerson, Tom \(*IC\)" <Tom.Emerson at wbconsultant.com> writes:

> I've got an "interesting" problem -- ... I'm not even sure how to
> convert "11/02/2008 + 3" to get "11/16/2008" ...

Trying to do arithmetic directly on "civil" dates like "11/02/2008" is
a nightmare even worse than trying to do arithmetic directly on Roman
numerals.  Better to convert to simple "Julian" dates, do arithmetic,
then convert back.

For example, what if the schedule spans February 28?  The arithmetic
function would have to embody the Gregorian rules for leap years.
Standard Unix utilities accommodate these details.

See "info coreutils 'date invocation'".

Illustrating converting your sample date to a count of seconds since
Unix epoch:

$ date +%s --date="11/02/2008"

Illustrating converting such a count back to a "civil" notation:

$ date +%m/%d/%Y --date=@1225609200

Noting from "$ echo '60 60 * 24 * 7 * 2 * p' | dc" that your example 2
weeks is 1209600 seconds, a naive addition of 2 weeks to 11/02/2008 is

$ date +%m/%d/%Y --date=@$(echo $(date +%s --date="11/02/2008") 1209600 + p | dc)

Not quite 11/16/2008 because this naively ignores the switch from
daylight savings time occurring within the schedule.  Fixing this is
an exercise for the reader.  (Hint: use noon rather than midnight.)

> I think the real killer here is that when a calculated date appears
> in the "blackout" table, that date and all subsequent dates need to
> increase their offset by an additional week...

...which will be enormously easier in the Julian domain.

- -- 
 John E. Kreznar jek at ininx.com 9F1148454619A5F08550 705961A47CC541AFEF13
  Politics is about forcible subjugation of dissidents.  Peaceful people
  don't do politics.

Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Processed by Mailcrypt 3.5.8+ <http://mailcrypt.sourceforge.net/>


More information about the SGVLUG mailing list