Re: Some clarification about TIMESTAMP
От | Steve Crawford |
---|---|
Тема | Re: Some clarification about TIMESTAMP |
Дата | |
Msg-id | 4DE67CC9.7020801@pinpointresearch.com обсуждение исходный текст |
Ответ на | Some clarification about TIMESTAMP (hernan gonzalez <hgonzalez@gmail.com>) |
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: