Thread: TIMESTAMP vs TIMESTAMP WITHOUT TIME ZONE
Hello, I have question about functionality for TIMESTAMP (with/without time zone). The main question is, what is better for usage: timemestamp WITH or WITHOUT time zone? I have e.g. server in USA and there is 6:00 a.m. Then I have client somwhere in Europe (+7 hour) and I read timestamp from server in USA. Figure 1: (using pure TIMESTAMP type) ------------------------------------- If I have defined column as: my_date TIMESTAMP and then I read current timestamp from the server (in USA) - it means I will use LOCALTIMESTAMP, I will receive at the client computer current timestamp value from USA - e.g. 10:00 a.m. But in the Europe is time 17:00 (+ 7 hour) and maybe this can cause problems. Figure 2: (using pure TIMESTAMP WITH TIME ZONE type) ---------------------------------------------------- If I have defined column as: my_date TIMESTAMP WITH TIME ZONE and then I read current timestamp from the server (in USA) - it means I will use CURRENT_TIMESTAMP or now(). Then I will receive at the client computer current timestamp value from USA - e.g. 10:00 a.m. and this timestamp will be automatically convrerted to the correct time zone (17:00 in Europe) ? It means when I send it back from europe client to the usa server, it will be also automatically converted to the usa timestamp? Is that right or I have didn't understand this behaviour? Is it better in generally to use timestamp with time zone or timestamp without time zone? Thanks for answer, with best regards, Julian Legeny mailto:juleni@livetrade.cz
On Thu, Nov 10, 2005 at 04:37:36PM +0100, juleni@livetrade.cz wrote: > Hello, > > I have question about functionality for TIMESTAMP (with/without time zone). > The main question is, what is better for usage: timemestamp WITH or WITHOUT > time zone? > > I have e.g. server in USA and there is 6:00 a.m. Then I have client somwhere in > Europe (+7 hour) and I read timestamp from server in USA. They serve different purposes. A timestamp without timezone is for storing times values you want to appear the same to everyone no matter where they are. A timestamp with timezone reprentents and instant in time and will be converted to the time appropriate for the local user. So it all depends on what you want. You can convert between them: my_timestamp_with_timezone AT TIME ZONE 'Europe/Amsterdam' returns that the wallclock time of that timestamp as it was in Amsterdam then. The other way works too. Hope this helps, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
Attachment
juleni@livetrade.cz writes:
> I have question about functionality for TIMESTAMP (with/without time zone).
> The main question is, what is better for usage: timemestamp WITH or WITHOUT
> time zone?
You almost certainly want to store timestamp WITH time zone. It's
really poor design that the SQL spec defines plain "timestamp" to mean
the other.
regards, tom lane
Thanks for answer, I will use TIMESTAMP WITH TIMEZONE. -- Best regards, Julian Legeny mailto:juleni@livetrade.cz ========================================================================= On Thu, Nov 10, 2005 at 04:37:36PM +0100, juleni@livetrade.cz wrote: > Hello, > > I have question about functionality for TIMESTAMP (with/without time zone). > The main question is, what is better for usage: timemestamp WITH or WITHOUT > time zone? > > I have e.g. server in USA and there is 6:00 a.m. Then I have client somwhere in > Europe (+7 hour) and I read timestamp from server in USA. They serve different purposes. A timestamp without timezone is for storing times values you want to appear the same to everyone no matter where they are. A timestamp with timezone reprentents and instant in time and will be converted to the time appropriate for the local user. So it all depends on what you want. You can convert between them: my_timestamp_with_timezone AT TIME ZONE 'Europe/Amsterdam' returns that the wallclock time of that timestamp as it was in Amsterdam then. The other way works too. Hope this helps, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them. ========================================================================= juleni@livetrade.cz writes: > I have question about functionality for TIMESTAMP (with/without time zone). > The main question is, what is better for usage: timemestamp WITH or WITHOUT > time zone? You almost certainly want to store timestamp WITH time zone. It's really poor design that the SQL spec defines plain "timestamp" to mean the other. regards, tom lane ========================================================================= Thursday, November 10, 2005, 4:37:36 PM, you wrote: jlc> Hello, jlc> I have question about functionality for TIMESTAMP (with/without time zone). jlc> The main question is, what is better for usage: timemestamp WITH or WITHOUT jlc> time zone? jlc> I have e.g. server in USA and there is 6:00 a.m. Then I have client somwhere in jlc> Europe (+7 hour) and I read timestamp from server in USA. jlc> Figure 1: (using pure TIMESTAMP type) jlc> ------------------------------------- jlc> If I have defined column as: jlc> my_date TIMESTAMP jlc> and then I read current timestamp from the server (in USA) - it means I will use jlc> LOCALTIMESTAMP, I will receive at the client computer current timestamp value jlc> from USA - e.g. 10:00 a.m. But in the Europe is time 17:00 (+ 7 hour) and maybe jlc> this can cause problems. jlc> Figure 2: (using pure TIMESTAMP WITH TIME ZONE type) jlc> ---------------------------------------------------- jlc> If I have defined column as: jlc> my_date TIMESTAMP WITH TIME ZONE jlc> and then I read current timestamp from the server (in USA) - it means I will use jlc> CURRENT_TIMESTAMP or now(). Then I will receive at the client computer current jlc> timestamp value from USA - e.g. 10:00 a.m. and this timestamp will be jlc> automatically convrerted to the correct time zone (17:00 in Europe) ? It means jlc> when I send it back from europe client to the usa server, it will be also jlc> automatically converted to the usa timestamp? jlc> Is that right or I have didn't understand this behaviour? Is it better in jlc> generally to use timestamp with time zone or timestamp without time zone? jlc> Thanks for answer, jlc> with best regards, jlc> Julian Legeny jlc> mailto:juleni@livetrade.cz
On Thu, Nov 10, 2005 at 06:01:22PM +0100, juleni@livetrade.cz wrote: > Thanks for answer, I will use TIMESTAMP WITH TIMEZONE. The only thing I would add is you don't seem to be able to index a part of the timestamp with time zone value. As an example, I had a table with around 10m rows where I wanted to query by date. In order to add an index liki date_part(mytimestamp), you need to use timestamp without time zone. S -- http://www.cm.nu/~shane/
On Thu, Nov 10, 2005 at 02:44:28PM -0800, Shane wrote:
> The only thing I would add is you don't seem to be able to
> index a part of the timestamp with time zone value. As an
> example, I had a table with around 10m rows where I wanted
> to query by date. In order to add an index liki
> date_part(mytimestamp), you need to use timestamp without
> time zone.
Indexing part of a timestamp with time zone would be ambiguous.
For example, what date would you index for 2005-11-10 23:00:00-10?
If you're in Hawaii that moment's date is 2005-11-10, but nearly
everywhere else it's 2005-11-11. You can cheat by wrapping
date-extracting code in your own immutable function and indexing
on that function, but be sure you understand the problem with doing
so. Example:
CREATE FUNCTION mydate(timestamptz) RETURNS date AS $$
SELECT date($1);
$$ LANGUAGE sql IMMUTABLE STRICT;
CREATE TABLE foo (
id serial PRIMARY KEY,
ts timestamp with time zone NOT NULL
);
CREATE INDEX foo_date_idx ON foo (mydate(ts));
SET timezone TO 'US/Hawaii';
INSERT INTO foo (ts) VALUES ('2005-11-10 23:00:00-10');
SELECT * FROM foo;
id | ts
----+------------------------
1 | 2005-11-10 23:00:00-10
(1 row)
SELECT * FROM foo WHERE mydate(ts) = '2005-11-10';
id | ts
----+------------------------
1 | 2005-11-10 23:00:00-10
(1 row)
SET timezone TO 'Asia/Tokyo';
SELECT * FROM foo;
id | ts
----+------------------------
1 | 2005-11-11 18:00:00+09
(1 row)
SELECT * FROM foo WHERE mydate(ts) = '2005-11-11';
id | ts
----+----
(0 rows)
SET enable_indexscan TO off;
SELECT * FROM foo WHERE mydate(ts) = '2005-11-11';
id | ts
----+------------------------
1 | 2005-11-11 18:00:00+09
(1 row)
--
Michael Fuhr