Обсуждение: Re: Timestamp without time zone
>>>>> "PS" == Pasi Salminen <pasi.salminen@khf.fi> writes: PS> I am trying to create table with a field which type is timestamp PS> and I don't want it to be timestamp with time zone. This question has been asked at least 4 times since I've been following this list, including once by me. Appaarently, nobody seems to have an answer. The docs imply that type timstamp is without timezone, since there is a type "timestamp with timezone". If you do a "\d tablename" in psql, it shows "timestamp with timezone" on all your fields you declare as timestamp. This leads me to believe that they are silently "upgraded" in their type. I have a subroutine in my apps that strips the timezone before passing it along to the users. -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Vivek Khera, Ph.D. Khera Communications, Inc. Internet: khera@kciLink.com Rockville, MD +1-240-453-8497 AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/
You can't. Docs are slightly misleading, timestamp _has_ timezone, the [with time zone] syntax is only optional to explicitly specify that TZ is there. At any case, I have never ran into a problem using timezone, and never needed to 'strip it'. Since you probably never display bare output of "select blah" anyway, but format the time in your own unique way, there you can remove timezone. Why do you _want_ to remove timezone from values in database? There is definitely a way to work around whatever problem you are having. On 14 Jun 2001, Vivek Khera wrote: > >>>>> "PS" == Pasi Salminen <pasi.salminen@khf.fi> writes: > > PS> I am trying to create table with a field which type is timestamp > PS> and I don't want it to be timestamp with time zone. > > This question has been asked at least 4 times since I've been > following this list, including once by me. > > Appaarently, nobody seems to have an answer. The docs imply that type > timstamp is without timezone, since there is a type "timestamp with > timezone". If you do a "\d tablename" in psql, it shows "timestamp > with timezone" on all your fields you declare as timestamp. This > leads me to believe that they are silently "upgraded" in their type. > > I have a subroutine in my apps that strips the timezone before passing > it along to the users. > >
>>>>> "AP" == Alex Pilosov <alex@pilosoft.com> writes:
AP> Why do you _want_ to remove timezone from values in database? There is
AP> definitely a way to work around whatever problem you are having.
Because my normal date parsing routines from perl's Date::Parse
library don't recognize it, and fail to parse the date.
% perl -MDate::Parse -e 'print str2time("2001-06-14 12:00:00+04"),"\n";'
% perl -MDate::Parse -e 'print str2time("2001-06-14 12:00:00"),"\n";'
992534400
--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D. Khera Communications, Inc.
Internet: khera@kciLink.com Rockville, MD +1-240-453-8497
AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/
Vivek Khera <khera@kcilink.com> writes:
> "PS" == Pasi Salminen <pasi.salminen@khf.fi> writes:
> PS> I am trying to create table with a field which type is timestamp
> PS> and I don't want it to be timestamp with time zone.
> This question has been asked at least 4 times since I've been
> following this list, including once by me.
> Appaarently, nobody seems to have an answer. The docs imply that type
> timstamp is without timezone, since there is a type "timestamp with
> timezone".
In 7.1, "timestamp with timezone" is the official name of type
"timestamp". The datatype is the same as it's always been: it is a
timestamp stored internally as UTC (GMT), with *no* local timezone
information. On display, a timestamp value is translated to whatever
timezone is currently specified by SET TIMEZONE, and that zone is also
assumed during input conversion to UTC if the input string doesn't give
a specific timezone.
This behavior doesn't exactly correspond to either of the SQL92-defined
timestamp types (with or without timezone), but Thomas Lockhart insisted
that "with timezone" was a nearer approximation than "without".
I didn't and still don't agree with that ... maybe Thomas will pop up
on this thread and explain the reasoning, because I can't follow it.
Both of the two SQL92-defined behaviors are sufficiently brain dead that
no one here is in a hurry to replicate them exactly, so it's only a
question of what we choose to call the type we have. As of 7.1, asking
for "timestamp with time zone" or "timestamp without time zone" will
both give you the exact same "timestamp" datatype. Personally I think
that's fine, but it should display as plain "timestamp" and lose the
confusing, pointless extra verbiage.
If you want more detail, you can find the arguments about this in the
pghackers mail archives, sometime last year IIRC.
regards, tom lane
Well, I guess you have to chop the string three times before passing it to
strftime....
On Thu, 14 Jun 2001, Vivek Khera wrote:
> >>>>> "AP" == Alex Pilosov <alex@pilosoft.com> writes:
>
> AP> Why do you _want_ to remove timezone from values in database? There is
> AP> definitely a way to work around whatever problem you are having.
>
> Because my normal date parsing routines from perl's Date::Parse
> library don't recognize it, and fail to parse the date.
>
> % perl -MDate::Parse -e 'print str2time("2001-06-14 12:00:00+04"),"\n";'
>
> % perl -MDate::Parse -e 'print str2time("2001-06-14 12:00:00"),"\n";'
> 992534400
>
>
Vivek Khera <khera@kcilink.com> writes:
> AP> Why do you _want_ to remove timezone from values in database? There is
> AP> definitely a way to work around whatever problem you are having.
> Because my normal date parsing routines from perl's Date::Parse
> library don't recognize it, and fail to parse the date.
Leaving aside the question of whether Date::Parse isn't broken and in
need of fixing (the timestamp output format *is* ISO compliant),
it would seem that your answer lies in using to_char() to format the
timestamp value the way your client code wants, rather than expecting
the default output format to exactly meet your needs. You're not really
asking for a timestamp without timezone in the database, you're asking
how to suppress the timezone during display.
regards, tom lane
select date_part('epoch',timestamp) from table;
will give you the same result without using str2time at all.
Did I miss something here?
At 11:28 AM 6/14/01 -0400, Vivek Khera wrote:
>>>>>> "AP" == Alex Pilosov <alex@pilosoft.com> writes:
>
>AP> Why do you _want_ to remove timezone from values in database? There is
>AP> definitely a way to work around whatever problem you are having.
>
>Because my normal date parsing routines from perl's Date::Parse
>library don't recognize it, and fail to parse the date.
>
>% perl -MDate::Parse -e 'print str2time("2001-06-14 12:00:00+04"),"\n";'
>
>% perl -MDate::Parse -e 'print str2time("2001-06-14 12:00:00"),"\n";'
>992534400
>
>--
>=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
>Vivek Khera, Ph.D. Khera Communications, Inc.
>Internet: khera@kciLink.com Rockville, MD +1-240-453-8497
>AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/