Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

The documentation on date/time data types state that timestamp with timezone takes 8 bytes, while time with timezone takes 12 bytes. They both have the same resolution (1 microsecond), and on the face of it timestamp with timezone is storing more information.

Can anyone explain this behavior?

I'm not planning on using time with timezone for reasons explained on the same page.

share|improve this question
add comment

1 Answer 1

up vote 3 down vote accepted

time with time zone stores microseconds (8 bytes) and the time zone (4 bytes). timestamp with time zone stores just the microseconds and converts the time zone at display time. Because of the conceptual weirdness of the time with time zone type, the time zone needs to be stored explicitly. You don't actually need 8 bytes to store the number of microseconds in a day, but 4 bytes wouldn't be enough. If you really wanted to, you could probably devise a more compact storage format for time with time zone, but in practice nobody cares.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.