I was just looking at the 8.3.1 documentation on the postgresql web
site.

According to the docs, timestamp with time zone takes less space than
time with time zone with the same resolution. Is this a documentation
bug? It makes no sense to me that by storing the date with the time you
can save 4 bytes.

__
Marc

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQBIA9bkUBr6u+c2wkERAtQOAJ4h5CHYiN2BY1OMGwM6bB LB4KG2lQCfVSbb
AFvdEgdhdBckbQk0c4LuTTQ=
=YXDC
-----END PGP SIGNATURE-----

Marc Munro <marc@bloodnok.com> writes:
> According to the docs, timestamp with time zone takes less space than
> time with time zone with the same resolution. Is this a documentation
> bug?

Nope.

regression=# select typname,typlen from pg_type where typname like 'time%tz';
typname | typlen
-------------+--------
timestamptz | 8
timetz | 12
(2 rows)

> It makes no sense to me that by storing the date with the time you
> can save 4 bytes.

Actually, your mistake is in imagining that timestamptz represents the
timezone explicitly ... it doesn't. If it did, it'd be at least as
large as timetz.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Tom Lane wrote:
> Marc Munro <marc@bloodnok.com> writes:

> > It makes no sense to me that by storing the date with the time you
> > can save 4 bytes.
>
> Actually, your mistake is in imagining that timestamptz represents the
> timezone explicitly ... it doesn't. If it did, it'd be at least as
> large as timetz.

Hmm, so timetz does that? Amazing ... I didn't know that.

Which means that storing date + timetz in two separate columns is not
quite the same as storing a timestamptz. Oops.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Alvaro Herrera <alvherre@commandprompt.com> writes:
> Which means that storing date + timetz in two separate columns is not
> quite the same as storing a timestamptz. Oops.

Quite so. Our docs already point out that timetz is really a completely
brain-damaged concept, anyway.

There's been some talk of adding an explicit zone representation to
timestamptz, but so far I haven't been convinced that it's worth
doubling the storage requirement (which is what it would take,
considering alignment...). ISTM that we have defined timestamptz
in such a way that it solves many real-world problems, and timestamp
also solves real-world problems, but the use-case for a timestamp plus
an explicit time zone is much less clear.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Mon, Apr 14, 2008 at 09:10:52PM -0400, Tom Lane wrote:

> Actually, your mistake is in imagining that timestamptz represents the
> timezone explicitly ... it doesn't.

Which really is a great pity :-(

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Mon, Apr 14, 2008 at 09:39:57PM -0400, Tom Lane wrote:

> considering alignment...). ISTM that we have defined timestamptz
> in such a way that it solves many real-world problems, and timestamp
> also solves real-world problems,
No doubt about it.

> but the use-case for a timestamp plus an explicit time
> zone is much less clear.

GNUmed is a medical record. We store data about patients.
Our public database is accessed from within different time
zones. Imagine a user from Los Angeles and another one from
San Francisco. Later on it is not readily apparent wherefrom
a particular entry was made unless we store the originating
timezone and/or location (which we do). Even if the location
is stored one cannot *easily* derive the appropriate
timezone from it (and thereby the local time of entering
data).

This is particularly important in medicine - expected
hormone levels (say, cortisol) are markedly different at
different times of the day. Say, when a doctor in London
enters a cortisol level measured at 7:00am his time which is
later displayed in a location B with local time = GMT + 8
hours the level will appear to have been taken at 3:00pm --
for which the level is out of bounds.

Of course, the actual time stored in the database in UTC is
quite correct - it was indeed 3pm in location B when it was
7am in London. But we need to know the original local time
(and also be able to know UTC since we want to correlate
times). What we do now is to explicitely store the original
timezone with the timestamp for individual doctor-patient
encounters which is a bit of leap-of-faith but works (one
would actually have to store the timezone for each and every
timestamptz field). We then use that information to say
"SELECT ... AT TIME ZONE ... FROM ...".

Another scenario: Recently Germany underwent the annual DST
change. Suddenly times in the EMR entered before DST
appeared shifted one hour forward. Which, technically, is
correct - it's the same UTC time as before the DST onset,
just shifted by one more hour. They'll appear shifted back
to correct times when we go back to non-DST time. But then
DST-entered times will appear shifted back, too, until DST
starts again.

Of course, all this is solvable by explicitely keeping track
of which timestamps mean what but it'd be a whole lot easier
if one could just say:

select value, ts_blood_drawn at original time zone
from lab_results
where type = 'cortisol';

I (for one) would happily store more bytes if that's what it
takes to reliably get at correct results (given the above
circumstances).

Yes, I know about tagged types but have shyed away from them
so far courtesy of them not being adjustable after the fact.

Should I be using a custom domain for this ?

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Tue, Apr 15, 2008 at 02:46:14PM +0200, Karsten Hilbert wrote:
> Of course, the actual time stored in the database in UTC is
> quite correct - it was indeed 3pm in location B when it was
> 7am in London. But we need to know the original local time
> (and also be able to know UTC since we want to correlate
> times).

I was under the impression that "timestamp without time zone" does
precisely this. It would be nicer if the docs highlighted the
differences, and reasons behind, the semantics between the two, instead
of focusing so much on the syntax. The "WITH TIME ZONE" variant is
described nicely:

For timestamp with time zone, the internally stored value is always
in UTC (Universal Coordinated Time, traditionally known as Greenwich
Mean Time, GMT). An input value that has an explicit time zone
specified is converted to UTC using the appropriate offset for that
time zone. If no time zone is stated in the input string, then it is
assumed to be in the time zone indicated by the system's timezone
parameter, and is converted to UTC using the offset for the timezone
zone.

When a timestamp with time zone value is output, it is always
converted from UTC to the current timezone zone, and displayed as
local time in that zone. To see the time in another time zone, either
change timezone or use the AT TIME ZONE construct (see Section
9.9.3).

But there doesn't seem to be any similar description of the "WITHOUT
TIME ZONE" option. It mentions:

the date/time fields in the input value [...] is not adjusted for time
zone.

But that's about all I could find. I think that the actual semantics
should be described and maybe a paragraph should be written highlighting
differences with an example. I'd be happy to write this if people
agree.

My reasoning goes something like this: The WITH and WITHOUT clauses
seem to be the opposite of my naive understanding of their purpose. I'd
think that if you specify WITH TIME ZONE then it means that the timezone
is important to me, and I want to deal with it myself. Whereas, the
WITHOUT TIME ZONE clause would suggest that the timezone isn't important
to me, and anything the database can do to make the problem go away the
better. What the spec says, and PG does, is actually the opposite. The
fact that this confusion can occur (and seems to occur reasonably often
based on previous posts to the mailing lists) suggests that the docs
should highlight the differences more clearly.

I'd also hazard a guess that we don't hear about it more because most
people just work within a single time zone and hence don't even notice
the difference between the two.


Sam

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Tue, Apr 15, 2008 at 02:46:14PM +0200, Karsten Hilbert wrote:
> Yes, I know about tagged types but have shyed away from them
> so far courtesy of them not being adjustable after the fact.

What do you mean by this? Adjustable in what way?

Truly, taggedtypes are a really useful feature but I think the chance
of them being in the main tree approximatly nil, which is enough reason
to stay away from them.

> Should I be using a custom domain for this ?

You just need to store the pair (time,zone), I don't think domains are
flexible enough for that. A complex type maybe (but then you've just
reinvented taggedtypes, inefficiently )

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFIBLDpIB7bNG8LQkwRAmEAAJ9OKLq0FELj7hEuGq7PdQ MEIe3zywCfb556
jmHzyyHYuXaJCn8uUbSy8zU=
=rFBW
-----END PGP SIGNATURE-----

On Tuesday 15 April 2008 6:31 am, Sam Mason wrote:
> On Tue, Apr 15, 2008 at 02:46:14PM +0200, Karsten Hilbert wrote:
> > Of course, the actual time stored in the database in UTC is
> > quite correct - it was indeed 3pm in location B when it was
> > 7am in London. But we need to know the original local time
> > (and also be able to know UTC since we want to correlate
> > times).
>
> I was under the impression that "timestamp without time zone" does
> precisely this. It would be nicer if the docs highlighted the
> differences, and reasons behind, the semantics between the two, instead
> of focusing so much on the syntax. The "WITH TIME ZONE" variant is
> described nicely:
>
> For timestamp with time zone, the internally stored value is always
> in UTC (Universal Coordinated Time, traditionally known as Greenwich
> Mean Time, GMT). An input value that has an explicit time zone
> specified is converted to UTC using the appropriate offset for that
> time zone. If no time zone is stated in the input string, then it is
> assumed to be in the time zone indicated by the system's timezone
> parameter, and is converted to UTC using the offset for the timezone
> zone.
>
> When a timestamp with time zone value is output, it is always
> converted from UTC to the current timezone zone, and displayed as
> local time in that zone. To see the time in another time zone, either
> change timezone or use the AT TIME ZONE construct (see Section
> 9.9.3).
>
> But there doesn't seem to be any similar description of the "WITHOUT
> TIME ZONE" option. It mentions:
>
> the date/time fields in the input value [...] is not adjusted for time
> zone.
>
> But that's about all I could find. I think that the actual semantics
> should be described and maybe a paragraph should be written highlighting
> differences with an example. I'd be happy to write this if people
> agree.
>
> My reasoning goes something like this: The WITH and WITHOUT clauses
> seem to be the opposite of my naive understanding of their purpose. I'd
> think that if you specify WITH TIME ZONE then it means that the timezone
> is important to me, and I want to deal with it myself. Whereas, the
> WITHOUT TIME ZONE clause would suggest that the timezone isn't important
> to me, and anything the database can do to make the problem go away the
> better. What the spec says, and PG does, is actually the opposite. The
> fact that this confusion can occur (and seems to occur reasonably often
> based on previous posts to the mailing lists) suggests that the docs
> should highlight the differences more clearly.
>
> I'd also hazard a guess that we don't hear about it more because most
> people just work within a single time zone and hence don't even notice
> the difference between the two.

My only comment is on this assertion. Any location that has DST rules has two
time zones. For instance I live in US PST/PDT. Without timezone support
doing date/time math across time zone boundaries is asking for problems.

>
>
> Sam

--
Adrian Klaver
aklaver@comcast.net

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Tue, Apr 15, 2008 at 02:31:22PM +0100, Sam Mason wrote:

> On Tue, Apr 15, 2008 at 02:46:14PM +0200, Karsten Hilbert wrote:
> > Of course, the actual time stored in the database in UTC is
> > quite correct - it was indeed 3pm in location B when it was
> > 7am in London. But we need to know the original local time
> > (and also be able to know UTC since we want to correlate
> > times).
>
> I was under the impression that "timestamp without time zone" does
> precisely this.
It doesn't. It keeps the time *value* untouched. But it
doesn't even store *any* timezone information with it. So,
unless I *know* the original timezone by any other means I
don't have *any* clue as to what point in time a particular
timestamp value is. It less useful than "with time zone". The
latter at least allows me to know the true (UTC-adjusted)
time of an event without jumping through any hoops.

> I'd also hazard a guess that we don't hear about it more because most
> people just work within a single time zone and hence don't even notice
> the difference between the two.

Any DST change will highlight the difference quite clearly.
I don't even have to change locations. Any tstz stored
before a DST changeover will (quite logically) show up as
shifted one hour after the changeover. This happens twice a
year.

A different angle:

Customer orders item at 23:15 on March 30. Item is on
special offer March 30th only. DST change happens on March
30 to March-31. Dealer looks at orders and sees "item
ordered March 31st 0:15" and does NOT apply the rebate for
March 30th.

Of course, it's the app developers fault, but the use case
for keeping the original timezone (so it can be reapplied)
is clearly there.

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general