Thread: 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/