Thread: How to timestamp

Hi Alex,

On Thu, 27 Dec 2001, Alex Cheung Tin Ka [CD] wrote:

> Dear everybody,
>     I would like to know how to create a timestamp value without time zone.
>     I have encountered a problem that I have set following values in the table.
>
>     create table test(
>          a timestamp without time zone

There is a data type without time zone. It's just called
'timestamp'

So try:

    create table test (a timestamp);
    insert into test (a) values (current_timestamp);
    select * from test;

and you will get something like:

     2002-01-05 13:40:29+01

According to PostgreSQL documentation all timestamp data is
displayed with time zone info in various flavours (ISO, SQL,
Postgres, national variants, ...). Data type 'timestamp'
stores it's data without timezone in contrast to data type
'timestamp with time zone'. For timestamp without time zone
PostgreSQL assumes time zone of your machine.

Timestamp data is 8 byte numeric data, what you see is a
converted character represenation. To get your own character
representaion of timestamp, use conversion function to_char(),
example:

    select to_char(a, 'YY-MM-DD HH24:MI:ss') from test;

could give you something like:

     2002-01-05 13:40:29

For more details have a look at Chapter 5 of PostgreSQL
documentation: 'Formatting Functions'


>     );
>     INSERT INTO test (a) VALUES(current_timestamp);
>
>     but the value of a always shows the zone value like this "2001-12-27 14:54:47+08"
>     How to get rid of the last "+08" value?
> ...

Hope this helps.

Martin

--
Dipl-Ing. Martin Jacobs * Germany
Registered Linux User #87175, http://counter.li.org/

Some slight clarifications...

> > I would like to know how to create a timestamp value without time zone.
> There is a data type without time zone. It's just called
> 'timestamp'

This is mostly true for the upcoming version 7.2, but is not the case
for older versions (it could be, and probably should be, but isn't ;).
Here are some details on the differences, and how those differences are
going away:

o timestamp with/without time zone all map to the same "timezone aware"
data type in versions prior to 7.2.

o For 7.2, "timestamp" maps to "timestamp with time zone" to help with
upgrading from 7.1 and before. For 7.3, it is likely that we will
conform more closely to the SQL9x standard and have it map to "timestamp
without time zone". But imho y'all should be using timezones in most
cases anyway. You can get "timestamp without time zone" by specifying it
explicitly.

o In any version, you can control which time zone is used by your
application. So if you don't want it shifting around to reflect local
reality, then you can force it to a specific offset or to zero. Things
will behave pretty much like a "zoneless" data type.

hth

                 - Thomas