Thread: Some clarification about TIMESTAMP
I'm doing some tests with date-time related fields to design my web
application.
I was already dissatisfied with Postgresql handling of timezones
concepts (issue
already discussed here - not entirely PG's fault, rather a SQL thing)
and I vehemently
reject the idea of a global server-side timezone configuration having
any infuence on
my DB layer, so I am planning to use always plain TIMESTAMP data tipe
(with no TIMEZONE).
What I want is that a TIMESTAMP field to be conceptually equivalent to
a plain {YEAR,MONTH,DAY HH,MM,SS},
data-tuple, i.e. a "local time". To clarifiy, for me "local time" =
"timezone unkown". Which is is NOT the same
as assuming some default (OS or database) timezone. It might very well
happen that I store in a -say- ALARM_TIME two datetimes
that correspond to users that have different ("local") timezones. So,
I want '2011-05-31 10:00:00' in this field
to mean 10.00 AM in some UNKNOWN timezone (that of a particular user).
In this scenario, I assumed the natural convention is: store just a
UTC time, using a TIMESTAMP. I believe that's the idea
of a plain TIMESTAMP.
However, I'm not sure if I can get a totally timezone-indepent behaviour:
CREATE TABLE t1 ( ts timestamp without time zone);
db=# insert into t1 values('1970-01-01 00:00:00');
INSERT 0 1
db=# select ts,extract(epoch from ts) from t1;
ts | date_part
---------------------+-----------
1970-01-01 00:00:00 | 21600
I was dismayed to see this, I assumed that my insert has stored a unix
timestamp = 0.
It seems not?
But on the other side, if I modify the server timezone what gets
changed is the epoch calculation!
asdas=# SET TIMEZONE TO 'XXX11';
SET
asdas=# select ts,extract(epoch from ts) from t1;
ts | date_part
---------------------+-----------
1970-01-01 00:00:00 | 39600
Why? What is happening here?
--
Hernán J. González
http://hjg.com.ar/
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@postgresql.org] On Behalf Of hernan gonzalez
> Sent: Tuesday, May 31, 2011 12:45 PM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] Some clarification about TIMESTAMP
>
> I vehemently reject the idea of a global server-side timezone
configuration
> having any infuence on my DB layer, so I am planning to use always plain
> TIMESTAMP data tipe (with no TIMEZONE).
>
> In this scenario, I assumed the natural convention is: store just a UTC
time,
> using a TIMESTAMP. I believe that's the idea of a plain TIMESTAMP.
>
> However, I'm not sure if I can get a totally timezone-indepent behaviour:
>
> CREATE TABLE t1 ( ts timestamp without time zone); db=# insert into t1
> values('1970-01-01 00:00:00'); INSERT 0 1 db=# select ts,extract(epoch
from
> ts) from t1;
> ts | date_part
> ---------------------+-----------
> 1970-01-01 00:00:00 | 21600
>
> I was dismayed to see this, I assumed that my insert has stored a unix
> timestamp = 0.
> It seems not?
>
> But on the other side, if I modify the server timezone what gets changed
is
> the epoch calculation!
>
> asdas=# SET TIMEZONE TO 'XXX11';
> SET
> asdas=# select ts,extract(epoch from ts) from t1;
> ts | date_part
> ---------------------+-----------
> 1970-01-01 00:00:00 | 39600
>
> Why? What is happening here?
>
From the documentation for "extract":
"epoch
For date and timestamp values, the number of seconds since 1970-01-01
00:00:00 UTC (can be negative); for interval values, the total number of
seconds in the interval"
Since "epoch" is a timestamptz value (UTC) in order to subtract determine
the how many seconds has elapsed since the epoch the value being substracted
must be converted into a timestamptz. Since you stored a "local time"
without a timestamp when the conversion occurs it is done at local time (+6
or +11 in your examples).
David J.
hernan gonzalez <hgonzalez@gmail.com> writes:
> I'm doing some tests with date-time related fields to design my web
> application.
> I was already dissatisfied with Postgresql handling of timezones
> concepts (issue
> already discussed here - not entirely PG's fault, rather a SQL thing)
> and I vehemently
> reject the idea of a global server-side timezone configuration having
> any infuence on
> my DB layer, so I am planning to use always plain TIMESTAMP data tipe
> (with no TIMEZONE).
Frankly, that position seems pretty silly. There are any number of
server-side settings that can affect the interpretation (and display)
of your data. Datestyle for example already renders this position
untenable.
> However, I'm not sure if I can get a totally timezone-indepent behaviour:
> CREATE TABLE t1 ( ts timestamp without time zone);
> db=# insert into t1 values('1970-01-01 00:00:00');
> INSERT 0 1
> db=# select ts,extract(epoch from ts) from t1;
> ts | date_part
> ---------------------+-----------
> 1970-01-01 00:00:00 | 21600
> I was dismayed to see this, I assumed that my insert has stored a unix
> timestamp = 0.
> It seems not?
It did, but extract(epoch) assumes you want a distance from the real
Unix epoch, so it takes the timestamp as being in local zone. AFAIR
there isn't a function that does exactly what you seem to be thinking
of.
regards, tom lane
> There are any number of > server-side settings that can affect the interpretation (and display) > of your data. Datestyle for example already renders this position > untenable. What makes me a little uncomfortable in this assertion -and in many parts of PG docs- is that emphasis put on what "is displayed", as assuming that I will be using postgresql in the server, and using the command-line psql. But of course one frequently (mostly?) access the DB remotely and from a client interface (eg. JDBC), one would say that the display/interpret (from to a string) ocurrs normally in an upper layer, not in the DB. (I suspect, from some peeking at the source I did once, that internally the "canonical" representation of values in memory is as a string, the same that it's displayed/parsed in the psql - and so, even if I don't use the psql CLI, the convertion to/from string happens the same internally - is this true? that's far from obvious for me. Say: when I query a timestamp from the DB via JDBC to display in a jsp page, it first converted from the binary store format to a psql-like string, then the jdbc driver parses that string to convert it to a Date java object, and the finally Java converts it to a string again? Is that so?) > It did, but extract(epoch) assumes you want a distance from the real > Unix epoch, so it takes the timestamp as being in local zone. AFAIR > there isn't a function that does exactly what you seem to be thinking > of. Weel, it seems that if I want that timezone-agnostic behaviour, so that extract(epoch) always returns the same integer for a given stored value (and different server-configred timezones) I must use (no very intuitive) a TIMESTAMP WITH TIMEZONE. Regards -- Hernán J. González http://hjg.com.ar/
On Tue, May 31, 2011 at 10:45 AM, hernan gonzalez <hgonzalez@gmail.com> wrote: > In this scenario, I assumed the natural convention is: store just a > UTC time, using a TIMESTAMP. I believe that's the idea > of a plain TIMESTAMP. No a plain timestamp has no timezone, UTC or otherwise. it's more like what you'd use if you had a free pie special from noon to midnight every wednesday in all your restaurants across the US. A regular timestamp, no tz info, would say it's from 2011-06-01 12:00:00 to 2011-06-01 23:59:59. This would be noon to midnight in each timezone on its own. If you converted it from east coast to UTC, let's say, then it would be from 11 to 11 central, 10 to 10 mountain, and 9 to 9 west coast time. Folks in hawaii would have to order their free pie before 6pm. (BTW, this is exactly what one restaurant here in the US does). Timestamptz is taken from a given timezone and converted to UTC then stored as such.
On 05/31/2011 12:00 PM, hernan gonzalez wrote: >> There are any number of >> server-side settings that can affect the interpretation (and display) >> of your data. Datestyle for example already renders this position >> untenable. > > What makes me a little uncomfortable in this assertion -and in many > parts of PG docs- > is that emphasis put on what "is displayed", as assuming that I will > be using postgresql > in the server, and using the command-line psql. But of course one > frequently (mostly?) > access the DB remotely and from a client interface (eg. JDBC), one > would say that the > display/interpret (from to a string) ocurrs normally in an upper > layer, not in the DB. > (I suspect, from some peeking at the source I did once, that > internally the "canonical" > representation of values in memory is as a string, the same that it's > displayed/parsed > in the psql - and so, even if I don't use the psql CLI, the convertion > to/from string happens > the same internally - is this true? that's far from obvious for me. > Say: when I query a timestamp from the DB via JDBC to display in a jsp page, it > first converted from the binary store format to a psql-like string, > then the jdbc driver > parses that string to convert it to a Date java object, and the > finally Java converts > it to a string again? Is that so?) The string representation is generally how dates and times are passed from one program to another. It is the lowest common denominator and most programs have routines to convert the string into their own internal representation. >> It did, but extract(epoch) assumes you want a distance from the real >> Unix epoch, so it takes the timestamp as being in local zone. AFAIR >> there isn't a function that does exactly what you seem to be thinking >> of. > > Weel, it seems that if I want that timezone-agnostic behaviour, > so that extract(epoch) always returns the same integer for a given stored > value (and different server-configred timezones) I must use (no very intuitive) > a TIMESTAMP WITH TIMEZONE. The important part to understand is that a timestamp without timestamp values are unanchored in time. There are assumptions that come into play when the database works with them and we all know what assumptions do:) If you want to anchor a timestamp value to a particular point in time you need to use timestamp with timezone. > > Regards > -- Adrian Klaver adrian.klaver@gmail.com
On Tue, May 31, 2011 at 04:00:21PM -0300, hernan gonzalez wrote: > frequently (mostly?) > access the DB remotely and from a client interface (eg. JDBC), one > would say that the > display/interpret (from to a string) ocurrs normally in an upper > layer, not in the DB. In my experience, FWIW, handling dates in the upper layer is a good way to introduce subtle inconsistencies as different programmers use slightly different facilities in the date handling. I much prefer to work on systems where the date arithmetic is done with Postgres's date handling, and the result is just spit out to the application. This is obviously not always possible. OTOH, a need to do a lot of date manipulation up in the application can be -- not must, but can -- a clue that you have something wrong with your transaction handling model. I've often seen the strategy of putting all the business logic out in the application result in this sort of date handling, for instance, and that can often the the source of a large number of round trips as well. That said, > Weel, it seems that if I want that timezone-agnostic behaviour, > so that extract(epoch) always returns the same integer for a given stored > value (and different server-configred timezones) I must use (no very intuitive) > a TIMESTAMP WITH TIMEZONE. . . .yes. Do everything in UTC, and then you have the best of all worlds here. A -- Andrew Sullivan ajs@crankycanuck.ca
On 05/31/2011 09:45 AM, hernan gonzalez wrote:
> I'm doing some tests with date-time related fields to design my web
> application.
> I was already dissatisfied with Postgresql handling of timezones
> concepts (issue
> already discussed here - not entirely PG's fault, rather a SQL thing)
> and I vehemently
> reject the idea of a global server-side timezone configuration having
> any infuence on
> my DB layer, so I am planning to use always plain TIMESTAMP data tipe
> (with no TIMEZONE).
>
> What I want is that a TIMESTAMP field to be conceptually equivalent to
> a plain {YEAR,MONTH,DAY HH,MM,SS},
> data-tuple, i.e. a "local time". To clarifiy, for me "local time" =
> "timezone unkown". Which is is NOT the same
> as assuming some default (OS or database) timezone. It might very well
> happen that I store in a -say- ALARM_TIME two datetimes
> that correspond to users that have different ("local") timezones. So,
> I want '2011-05-31 10:00:00' in this field
> to mean 10.00 AM in some UNKNOWN timezone (that of a particular user).
>
> In this scenario, I assumed the natural convention is: store just a
> UTC time, using a TIMESTAMP. I believe that's the idea
> of a plain TIMESTAMP.
>
> However, I'm not sure if I can get a totally timezone-indepent behaviour:
All is well - everything is under your control. It just takes a bit of
time to understand how time calculations work.
> CREATE TABLE t1 ( ts timestamp without time zone);
> db=# insert into t1 values('1970-01-01 00:00:00');
> INSERT 0 1
> db=# select ts,extract(epoch from ts) from t1;
> ts | date_part
> ---------------------+-----------
> 1970-01-01 00:00:00 | 21600
>
> I was dismayed to see this, I assumed that my insert has stored a unix
> timestamp = 0.
> It seems not?
Understand that you have basically requested, whether you realized or
not, "at what instant, displayed as a UNIX epoch, will it be midnight
January 1, 1970 in this time zone". You, as the programmer, have
complete control over what time zone is used for such conversions and
PostgreSQL will do the work for you.
If you reverse the calculation, you will see that indeed it is midnight
January 1, 1970 in your location:
select abstime(21600);
abstime
------------------------
1970-01-01 00:00:00-06
Were you in England:
set timezone to 'UTC';
SET
select ts,extract(epoch from ts) from t1;
ts | date_part
---------------------+-----------
1970-01-01 00:00:00 | 0
Note: Most calculations that ask for a timestamp *without* time zone at
a specific time zone return a timestamp *with* time zone - you are
essentially adding timezone information to get a point in time.
Conversely, most calculations that ask for a timestamp *with* time zone
(point in time) at a specific zone return a timestamp *without* time
zone - you told it the time zone so you must only need the time stamp.
> But on the other side, if I modify the server timezone what gets
> changed is the epoch calculation!
>
> asdas=# SET TIMEZONE TO 'XXX11';
> SET
> asdas=# select ts,extract(epoch from ts) from t1;
> ts | date_part
> ---------------------+-----------
> 1970-01-01 00:00:00 | 39600
>
> Why? What is happening here?
See above re: the calculation but note that you are not modifying the
server, you are telling the server the time zone to use for date/time
calculations for this client - no other client is affected.
So choose the data type that is appropriate for your app. If you need to
represent specific points in time (the shuttle launched at..., the
teleconference starts at...) use a timestamp with time zone. For data
that is more "time of day" relevant (lunch is served at 11:30) use a
timestamp without time zone.
Before condemning PostgreSQL's date/time handling, read and reread the
section on date and time data-types and calculations. They are very
powerful, useful and logical once understood.
Cheers,
Steve