Re: Comment on timezone and interval types - Mailing list pgsql-general

Yes For example :

MST = GMT - 7 hours
MDT = GMT - 6 hours

The GMT time remains constant no mater if it is or is not
daylight savings time.

You still want to bill someone for 1 hour of usage from
02:00 MDT to 02:00 MST, but you don't want to bill an
hour from 02:00 MST to 03:00 MDT.

Unless you are using GMT or another timezone that does not
use daylight savings, you should always include the timezone
with the time.

1 day should always be calculated as 24 hours, just as an hour
is calculated as 60 minutes...

Since interval does not store an actual time range, it is not sensitive to
daylight savings.

Where problems occur is when you try to use units larger than a week
because they vary in the number of days per unit depending on the date
range.

I would prefer to see interval state time in :

Days:Hours:Minutes:Seconds.Microseconds

Rather than :

Years Months Days Hours:Minutes:Seconds.Microseconds

Since months and years are not a constant number of days it does not
seem reasonable to use them in calculations to determine days, unless
it is qualified with a start or stop time and date including the time zone.

Since I don't need to account for microseconds or durations larger
than +/- 68 years I usually use an int4 to store time usage in seconds.
Since int4 can be cast into reltime, it is simple to calculate the
beginning or end of the interval with one timestamp with timezone and
an int4 duration. The Storage required for this is 16 bytes ; 12 for the
timestamp and 4 for the int4 {integer}. If you need more accuracy
you could use a timestamp and an interval, but the storage required
would be 24 bytes IIRC.

Stuart Bishop wrote:

> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Bruno Wolff III wrote:
> | Recently there has been some discussion about attaching a timezone to
> | a timestamp and some other discussion about including a 'day' part
> | in the interval type. These two features impact each other, since
> | if you add a 'day' to a timestamp the result can depend on what
> timezone
> | the timestamp is supposed to be in. It probably makes more sense to use
> | a timezone associated with the timestamp than say the timezone GUC
> or the
> | fixed timezone UTC.
>
> If you add a 'day' to a timestamp, it should be identical to adding 24
> hours. Any other interpretation leads to all sorts of wierd ambiguities.
> For example, what is '2am April 3rd 2004 US/Eastern + 1 day'? 2am on
> April 4th 2004 didn't exist in that timezone because the clocks were put
> forward and that hour skipped. If you round up to the nearest existant
> time, you then have the issue that '2am April 3rd + 1 day == 3am Aril
> 3rd + 1 day'.
>
> - --
> Stuart Bishop <stuart@stuartbishop.net>
> http://www.stuartbishop.net/
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.2.4 (GNU/Linux)
>
> iD8DBQFBge+sAfqZj7rGN0oRAgInAJsEuYkxX6/jsaszquhjEX/PH3nXvACfVBW9
> Z3sfU5XGgxSOI77vuOOOzKA=
> =euY6
> -----END PGP SIGNATURE-----
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
> .
>

--
Guy Fraser
Network Administrator
The Internet Centre
780-450-6787 , 1-888-450-6787

There is a fine line between genius and lunacy, fear not, walk the
line with pride. Not all things will end up as you wanted, but you
will certainly discover things the meek and timid will miss out on.