pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Timestamp without time zone
8+ messages / 5 participants
[nested] [flat]

* Timestamp without time zone
@ 2001-06-14 08:12  Pasi Salminen <pasi.salminen@khf.fi>
  0 siblings, 1 reply; 8+ messages in thread

From: Pasi Salminen @ 2001-06-14 08:12 UTC (permalink / raw)
  To: pgsql-sql

I am trying to create table with a field which type is timestamp
and I don't want it to be timestamp with time zone.

If I create the table with the following statement,
field type becomes timestamp with time zone:

CREATE TABLE "test" ("timest" timestamp DEFAULT now());

How can I make a field which type is "timestamp without time zone"?

Regards.

Pasi Salminen





^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Timestamp without time zone
@ 2001-06-14 14:57  Vivek Khera <khera@kcilink.com>
  parent: Pasi Salminen <pasi.salminen@khf.fi>
  0 siblings, 2 replies; 8+ messages in thread

From: Vivek Khera @ 2001-06-14 14:57 UTC (permalink / raw)
  To: pgsql-sql

>>>>> "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/



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Re: Timestamp without time zone
@ 2001-06-14 15:28  Vivek Khera <khera@kcilink.com>
  parent: Alex Pilosov <alex@pilosoft.com>
  1 sibling, 2 replies; 8+ messages in thread

From: Vivek Khera @ 2001-06-14 15:28 UTC (permalink / raw)
  To: pgsql-sql

>>>>> "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/



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Re: Timestamp without time zone
@ 2001-06-14 15:30  Alex Pilosov <alex@pilosoft.com>
  parent: Vivek Khera <khera@kcilink.com>
  1 sibling, 2 replies; 8+ messages in thread

From: Alex Pilosov @ 2001-06-14 15:30 UTC (permalink / raw)
  To: Vivek Khera <khera@kcilink.com>; +Cc: pgsql-sql

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.
> 
> 




^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Re: Timestamp without time zone
@ 2001-06-14 16:15  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Vivek Khera <khera@kcilink.com>
  1 sibling, 0 replies; 8+ messages in thread

From: Tom Lane @ 2001-06-14 16:15 UTC (permalink / raw)
  To: Vivek Khera <khera@kcilink.com>; +Cc: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Re: Timestamp without time zone
@ 2001-06-14 17:22  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Vivek Khera <khera@kcilink.com>
  1 sibling, 0 replies; 8+ messages in thread

From: Tom Lane @ 2001-06-14 17:22 UTC (permalink / raw)
  To: Vivek Khera <khera@kcilink.com>; +Cc: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Re: Timestamp without time zone
@ 2001-06-14 17:30  Alex Pilosov <alex@pilosoft.com>
  parent: Vivek Khera <khera@kcilink.com>
  1 sibling, 0 replies; 8+ messages in thread

From: Alex Pilosov @ 2001-06-14 17:30 UTC (permalink / raw)
  To: Vivek Khera <khera@kcilink.com>; +Cc: pgsql-sql

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
> 
> 




^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Re: Timestamp without time zone
@ 2001-06-14 21:55  Frank Bax <fbax@sympatico.ca>
  parent: Alex Pilosov <alex@pilosoft.com>
  1 sibling, 0 replies; 8+ messages in thread

From: Frank Bax @ 2001-06-14 21:55 UTC (permalink / raw)
  To: Vivek Khera <khera@kcilink.com>; +Cc: pgsql-sql

	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/



^ permalink  raw  reply  [nested|flat] 8+ messages in thread


end of thread, other threads:[~2001-06-14 21:55 UTC | newest]

Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-06-14 08:12 Timestamp without time zone Pasi Salminen <pasi.salminen@khf.fi>
2001-06-14 14:57 ` Vivek Khera <khera@kcilink.com>
2001-06-14 15:30   ` Alex Pilosov <alex@pilosoft.com>
2001-06-14 15:28     ` Vivek Khera <khera@kcilink.com>
2001-06-14 17:22       ` Tom Lane <tgl@sss.pgh.pa.us>
2001-06-14 17:30       ` Alex Pilosov <alex@pilosoft.com>
2001-06-14 21:55     ` Frank Bax <fbax@sympatico.ca>
2001-06-14 16:15   ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox