[SGVLUG] Can SQL even do this?

[John E. Kreznar]

-----BEGIN PGP SIGNED MESSAGE-----
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"
1225609200

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

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

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)
11/15/2008

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.

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

iD8DBQFJB9ibYaR8xUGv7xMRAn0EAKCA6nXFaPmG4YacnH/VWkSMkXssQACcDPvP
EwqZ1JdZtbXBDZJ2rbbum9U=
=UGnh
-----END PGP SIGNATURE-----