Обсуждение: timestamp wiht time zone
Hi,
i have a column in my table defined like this:
time_stamp timestamp DEFAULT ('now'::text)::timestamp with time zone
Data gets filled in this column from a Unix tools server.
Example values of this field are:
time_stamp
--------------------------------------------
2007-06-27 14:52:14.760133
2007-06-27 15:06:56.90582
I have the following questions on this field.
1. What is the value after the dot (period) at the end. Like 760133 and 90582
2. How does it talk about the time zone.
Also, the reason I'm using time zone is that I have to compare different values in the tables correctly without any error(s)
based on time zones.
Any kind of help would be greatly appreciated.
Thanks,
~Jas
i have a column in my table defined like this:
time_stamp timestamp DEFAULT ('now'::text)::timestamp with time zone
Data gets filled in this column from a Unix tools server.
Example values of this field are:
time_stamp
--------------------------------------------
2007-06-27 14:52:14.760133
2007-06-27 15:06:56.90582
I have the following questions on this field.
1. What is the value after the dot (period) at the end. Like 760133 and 90582
2. How does it talk about the time zone.
Also, the reason I'm using time zone is that I have to compare different values in the tables correctly without any error(s)
based on time zones.
Any kind of help would be greatly appreciated.
Thanks,
~Jas
Jasbinder Singh Bali escribió: > Hi, > i have a column in my table defined like this: > > time_stamp timestamp DEFAULT ('now'::text)::timestamp with time zone Note that the column is of type timestamp, which _doesn't_ have a time zone. You probably want time_stamp timestamp with time zone DEFAULT ('now'::text)::timestamp with time zone > 1. What is the value after the dot (period) at the end. Like 760133 and > 90582 milliseconds > 2. How does it talk about the time zone. It doesn't because the time zone information is not being stored due to the datatype issue I mentioned above. Note: the time zone is not actually stored. What actually happens is that the value is "rotated" to GMT and stored as a GMT value, and then when you extract it from the database it is "rotated" to the current TimeZone for display. If you need to store what time zone a value "is in" you need to store that information in a separate column. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
Thanks Alvaro,
Your information proved very handy.
~Jas
Your information proved very handy.
~Jas
On 6/27/07, Alvaro Herrera <alvherre@commandprompt.com > wrote:
Jasbinder Singh Bali escribió:
> Hi,
> i have a column in my table defined like this:
>
> time_stamp timestamp DEFAULT ('now'::text)::timestamp with time zone
Note that the column is of type timestamp, which _doesn't_ have a time
zone. You probably want
time_stamp timestamp with time zone DEFAULT ('now'::text)::timestamp with time zone
> 1. What is the value after the dot (period) at the end. Like 760133 and
> 90582
milliseconds
> 2. How does it talk about the time zone.
It doesn't because the time zone information is not being stored due to
the datatype issue I mentioned above.
Note: the time zone is not actually stored. What actually happens is
that the value is "rotated" to GMT and stored as a GMT value, and then
when you extract it from the database it is "rotated" to the current
TimeZone for display. If you need to store what time zone a value "is
in" you need to store that information in a separate column.
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.