Обсуждение: time stamp

Поиск
Список
Период
Сортировка

time stamp

От
"nimeshb"
Дата:
The datatype "timestamp" automatically adds zone in the field values.
i.e. If you insert a value 06-05-2002 15:03:08 (MM/DD/YYYY HH/MM/SS) it would store as "06-05-2002 15:03:08-05". I don't want
last two digits 05 which represents timezone and determines how many hrs you are ahead(or behind?) GMT.
Is there anyway to get rid of these digits(representing Zone)
Nimesh
 

Re: time stamp

От
Brian McCane
Дата:
CREATE TABLE test (ts TIMESTAMP WITHOUT TIME ZONE) ;


On Wed, 19 Jun 2002, nimeshb wrote:

> The datatype "timestamp" automatically adds zone in the field values.
> i.e. If you insert a value 06-05-2002 15:03:08 (MM/DD/YYYY HH/MM/SS) it would store as "06-05-2002 15:03:08-05". I
don'twant 
> last two digits 05 which represents timezone and determines how many hrs you are ahead(or behind?) GMT.
> Is there anyway to get rid of these digits(representing Zone)
> Nimesh
>
>

Wm. Brian McCane                    | Life is full of doors that won't open
Search http://recall.maxbaud.net/   | when you knock, equally spaced amid those
Usenet http://freenews.maxbaud.net/ | that open when you don't want them to.
Auction http://www.sellit-here.com/ | - Roger Zelazny "Blood of Amber"


Re: time stamp

От
"nimeshb"
Дата:
CREATE TABLE test (ts TIMESTAMP WITHOUT TIME ZONE) ; doesn't work. It
creates that table but
when you insert a value without time zone it stll appends -05 as time
zone(my datestyle is US and since my time is EST it appends 05 on its own
and that sucks )


----- Original Message -----
From: "Brian McCane" <bmccane@mccons.net>
To: "nimeshb" <nimeshb@syscon-intl.com>
Cc: <pgsql-admin@postgresql.org>
Sent: Wednesday, June 19, 2002 6:32 PM
Subject: Re: [ADMIN] time stamp


>
> CREATE TABLE test (ts TIMESTAMP WITHOUT TIME ZONE) ;
>
>
> On Wed, 19 Jun 2002, nimeshb wrote:
>
> > The datatype "timestamp" automatically adds zone in the field values.
> > i.e. If you insert a value 06-05-2002 15:03:08 (MM/DD/YYYY HH/MM/SS) it
would store as "06-05-2002 15:03:08-05". I don't want
> > last two digits 05 which represents timezone and determines how many hrs
you are ahead(or behind?) GMT.
> > Is there anyway to get rid of these digits(representing Zone)
> > Nimesh
> >
> >
>
> Wm. Brian McCane                    | Life is full of doors that won't
open
> Search http://recall.maxbaud.net/   | when you knock, equally spaced amid
those
> Usenet http://freenews.maxbaud.net/ | that open when you don't want them
to.
> Auction http://www.sellit-here.com/ | - Roger Zelazny "Blood of Amber"
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>


Re: time stamp

От
"Mark McEahern"
Дата:
> CREATE TABLE test (ts TIMESTAMP WITHOUT TIME ZONE) ; doesn't work. It
> creates that table but
> when you insert a value without time zone it stll appends -05 as time
> zone(my datestyle is US and since my time is EST it appends 05 on its own
> and that sucks )

Why do you care how pg stores the field?  Are you able to retrieve it and do
the stuff you need to do?

// m

-


Re: time stamp

От
"nimeshb"
Дата:
I am  migrating our database from ingres to postgres and I want a replica of
ingres database into postgres for
this I had to write a lot of scripts but I don't know how to take care of
this problem as postgres this
by default.When I retrieve it with a select query it comes with ZONE but you
are right may be
the programmers would be able to do whatever they want without any
problem.Thanks anyway !

Nimesh
----- Original Message -----
From: "Mark McEahern" <marklists@mceahern.com>
To: "nimeshb" <nimeshb@syscon-intl.com>; "Brian McCane" <bmccane@mccons.net>
Cc: <pgsql-admin@postgresql.org>
Sent: Thursday, June 20, 2002 10:54 AM
Subject: Re: [ADMIN] time stamp


> > CREATE TABLE test (ts TIMESTAMP WITHOUT TIME ZONE) ; doesn't work. It
> > creates that table but
> > when you insert a value without time zone it stll appends -05 as time
> > zone(my datestyle is US and since my time is EST it appends 05 on its
own
> > and that sucks )
>
> Why do you care how pg stores the field?  Are you able to retrieve it and
do
> the stuff you need to do?
>
> // m
>
> -
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>


Re: time stamp

От
Tom Lane
Дата:
"nimeshb" <nimeshb@syscon-intl.com> writes:
> CREATE TABLE test (ts TIMESTAMP WITHOUT TIME ZONE) ; doesn't work.

Oh?  I get

test72=# CREATE TABLE test (ts TIMESTAMP WITHOUT TIME ZONE) ;
CREATE
test72=# insert into test values(now());
INSERT 803798 1
test72=# insert into test values('2002-02-22 12:34:56');
INSERT 803799 1
test72=# select * from test;
             ts
----------------------------
 2002-06-20 12:46:47.030269
 2002-02-22 12:34:56
(2 rows)

test72=# select version();
                            version
---------------------------------------------------------------
 PostgreSQL 7.2.1 on hppa-hp-hpux10.20, compiled by GCC 2.95.3
(1 row)


            regards, tom lane

Re: time stamp

От
Felipe Nascimento
Дата:

I used to manage a database without Time Zone. Now I use PG, and this concept of using Time Zone in date data is new to me. I find it intersting, but I have the following doubt:

let's imagine that my server resides in Time Zone(TZ) "-00", and one user resides in TZ "-05". Let´s say that 12p.m. to the user is 3p.m. to the server, on the same day.

Let's say that the user inputs a date for a business meeting: "2002-06-20 12:00:00". The server will save "2002-06-20 12:00:00-00"?? If my application sends an e-mail for the user reminding him of the meeting one hour befor it, the server will send that email at 11 o'clock (server time) that is 2 o'clock to the user, that means, after the meeting.

My question is: how to manage this so I can send the email to the user at his 11 o'clock (8 a.m. server time)???

Tks
Felipe Nascimento

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: quinta-feira, 20 de junho de 2002 13:49
To: nimeshb
Cc: Brian McCane; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] time stamp

"nimeshb" <nimeshb@syscon-intl.com> writes:
> CREATE TABLE test (ts TIMESTAMP WITHOUT TIME ZONE) ; doesn't work.

Oh?  I get

test72=# CREATE TABLE test (ts TIMESTAMP WITHOUT TIME ZONE) ;
CREATE
test72=# insert into test values(now());
INSERT 803798 1
test72=# insert into test values('2002-02-22 12:34:56');
INSERT 803799 1
test72=# select * from test;
             ts            
----------------------------
 2002-06-20 12:46:47.030269
 2002-02-22 12:34:56
(2 rows)

test72=# select version();
                            version                           
---------------------------------------------------------------
 PostgreSQL 7.2.1 on hppa-hp-hpux10.20, compiled by GCC 2.95.3
(1 row)

                        regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Re: time stamp

От
"Mark McEahern"
Дата:
To be fair, you really ought to ask this question:

Suppose user A sets up a conference call with users B, C, and D, who each
live in:

    A: Tokyo
    B: Denver
    C: San Diego
    D: New York

The meeting is to occur at 1:00 PM tokyo time.  (Never mind for now whether
that makes any sense.)

Trick questions:

1.  How does the database store the time of the meeting?
2.  What if user D is traveling to London at the time of the meeting?  She
changes her locale on her computer to London time, will the database show
her the correct time?

However you solve this problem, it's not going to be by naively storing user
A's local time with no time zone information and leaving it at that.

;-)

// m

-----Original Message-----
From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org]On Behalf Of Felipe Nascimento
Sent: Thursday, June 20, 2002 1:32 PM
To: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] time stamp


I used to manage a database without Time Zone. Now I use PG, and this
concept of using Time Zone in date data is new to me. I find it intersting,
but I have the following doubt:
let's imagine that my server resides in Time Zone(TZ) "-00", and one user
resides in TZ "-05". Let´s say that 12p.m. to the user is 3p.m. to the
server, on the same day.
Let's say that the user inputs a date for a business meeting: "2002-06-20
12:00:00". The server will save "2002-06-20 12:00:00-00"?? If my application
sends an e-mail for the user reminding him of the meeting one hour befor it,
the server will send that email at 11 o'clock (server time) that is 2
o'clock to the user, that means, after the meeting.
My question is: how to manage this so I can send the email to the user at
his 11 o'clock (8 a.m. server time)???
Tks
Felipe Nascimento


-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: quinta-feira, 20 de junho de 2002 13:49
To: nimeshb
Cc: Brian McCane; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] time stamp


"nimeshb" <nimeshb@syscon-intl.com> writes:
> CREATE TABLE test (ts TIMESTAMP WITHOUT TIME ZONE) ; doesn't work.
Oh?  I get
test72=# CREATE TABLE test (ts TIMESTAMP WITHOUT TIME ZONE) ;
CREATE
test72=# insert into test values(now());
INSERT 803798 1
test72=# insert into test values('2002-02-22 12:34:56');
INSERT 803799 1
test72=# select * from test;
             ts
----------------------------
 2002-06-20 12:46:47.030269
 2002-02-22 12:34:56
(2 rows)
test72=# select version();
                            version
---------------------------------------------------------------
 PostgreSQL 7.2.1 on hppa-hp-hpux10.20, compiled by GCC 2.95.3
(1 row)


                        regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

-


Re: time stamp

От
Aaron Spiteri
Дата:

Interesting point.


Original Message dated 21/06/02, 4:31:39

Author: Felipe Nascimento <Felipe.Nascimento@multivalor.com.br>

Re: Re: [ADMIN] time stamp :




I used to manage a database without Time Zone. Now I use PG, and this concept of using Time Zone in date data is new to me. I find it intersting, but I have the following doubt:

let's imagine that my server resides in Time Zone(TZ) "-00", and one user resides in TZ "-05". Let´s say that 12p.m. to the user is 3p.m. to the server, on the same day.

Let's say that the user inputs a date for a business meeting: "2002-06-20 12:00:00". The server will save "2002-06-20 12:00:00-00"?? If my application sends an e-mail for the user reminding him of the meeting one hour befor it, the server will send that email at 11 o'clock (server time) that is 2 o'clock to the user, that means, after the meeting.

My question is: how to manage this so I can send the email to the user at his 11 o'clock (8 a.m. server time)???

Tks
Felipe Nascimento



-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: quinta-feira, 20 de junho de 2002 13:49
To: nimeshb
Cc: Brian McCane; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] time stamp



"nimeshb" <nimeshb@syscon-intl.com> writes:
> CREATE TABLE test (ts TIMESTAMP WITHOUT TIME ZONE) ; doesn't work.

Oh?  I get

test72=# CREATE TABLE test (ts TIMESTAMP WITHOUT TIME ZONE) ;
CREATE
test72=# insert into test values(now());
INSERT 803798 1
test72=# insert into test values('2002-02-22 12:34:56');
INSERT 803799 1
test72=# select * from test;
             ts            
----------------------------
 2002-06-20 12:46:47.030269
 2002-02-22 12:34:56
(2 rows)

test72=# select version();
                            version                           
---------------------------------------------------------------
 PostgreSQL 7.2.1 on hppa-hp-hpux10.20, compiled by GCC 2.95.3
(1 row)



                        regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Re: time stamp

От
Felipe Nascimento
Дата:
Anybody???



I used to manage a database without Time Zone. Now I use PG, and this concept of using Time Zone in date data is new to me. I find it intersting, but I have the following doubt:

let's imagine that my server resides in Time Zone(TZ) "-00", and one user resides in TZ "-05". Let´s say that 12p.m. to the user is 3p.m. to the server, on the same day.

Let's say that the user inputs a date for a business meeting: "2002-06-20 12:00:00". The server will save "2002-06-20 12:00:00-00"?? If my application sends an e-mail for the user reminding him of the meeting one hour befor it, the server will send that email at 11 o'clock (server time) that is 2 o'clock to the user, that means, after the meeting.

My question is: how to manage this so I can send the email to the user at his 11 o'clock (8 a.m. server time)???

Tks
Felipe Nascimento



-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: quinta-feira, 20 de junho de 2002 13:49
To: nimeshb
Cc: Brian McCane; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] time stamp



"nimeshb" <nimeshb@syscon-intl.com> writes:
> CREATE TABLE test (ts TIMESTAMP WITHOUT TIME ZONE) ; doesn't work.

Oh?  I get

test72=# CREATE TABLE test (ts TIMESTAMP WITHOUT TIME ZONE) ;
CREATE
test72=# insert into test values(now());
INSERT 803798 1
test72=# insert into test values('2002-02-22 12:34:56');
INSERT 803799 1
test72=# select * from test;
             ts            
----------------------------
 2002-06-20 12:46:47.030269
 2002-02-22 12:34:56
(2 rows)

test72=# select version();
                            version                           
---------------------------------------------------------------
 PostgreSQL 7.2.1 on hppa-hp-hpux10.20, compiled by GCC 2.95.3
(1 row)



                        regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Re: time stamp

От
Tom Lane
Дата:
Felipe Nascimento <Felipe.Nascimento@multivalor.com.br> writes:
> let's imagine that my server resides in Time Zone(TZ) "-00", and one user
> resides in TZ "-05". Let=B4s say that 12p.m. to the user is 3p.m. to the
> server, on the same day.

> Let's say that the user inputs a date for a business meeting: "2002-06-20
> 12:00:00". The server will save "2002-06-20 12:00:00-00"??

No, it won't.  If the user has TimeZone set to -05, as he should, then
his input of '2002-06-20 12:00:00' will be read as '2002-06-20 12:00:00-05'.
And it will be displayed to him that way.  But if someone else who
has TimeZone set differently looks at the stored value, it will be shown
to them properly converted into their timezone.

This all works exactly the same as ordinary Unix timekeeping --- in
essence, everything is GMT inside the system, and rotation into a
particular timezone happens on-the-fly when a timestamp value is
entered or displayed.  The TimeZone variable corresponds to the TZ
environment variable of Unix.

> My question is: how to manage this so I can send the email to the user at
> his 11 o'clock (8 a.m. server time)???=20

If you let the system do what it wants to do, it will do the right
thing.

            regards, tom lane

Re: time stamp

От
Felipe Nascimento
Дата:

Is this valid for Web applications where the db server's client is the web server? (web server resides on the same machine of the db server!)

Or this is valid only for client x server applications?

Felipe

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: sexta-feira, 21 de junho de 2002 12:14
To: Felipe Nascimento
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] time stamp

Felipe Nascimento <Felipe.Nascimento@multivalor.com.br> writes:
> let's imagine that my server resides in Time Zone(TZ) "-00", and one user
> resides in TZ "-05". Let=B4s say that 12p.m. to the user is 3p.m. to the
> server, on the same day.

> Let's say that the user inputs a date for a business meeting: "2002-06-20
> 12:00:00". The server will save "2002-06-20 12:00:00-00"??

No, it won't.  If the user has TimeZone set to -05, as he should, then
his input of '2002-06-20 12:00:00' will be read as '2002-06-20 12:00:00-05'.
And it will be displayed to him that way.  But if someone else who
has TimeZone set differently looks at the stored value, it will be shown
to them properly converted into their timezone.

This all works exactly the same as ordinary Unix timekeeping --- in
essence, everything is GMT inside the system, and rotation into a
particular timezone happens on-the-fly when a timestamp value is
entered or displayed.  The TimeZone variable corresponds to the TZ
environment variable of Unix.

> My question is: how to manage this so I can send the email to the user at
> his 11 o'clock (8 a.m. server time)???=20

If you let the system do what it wants to do, it will do the right
thing.

                        regards, tom lane

Re: time stamp

От
Thomas Swan
Дата:
Tom Lane wrote:
Felipe Nascimento <Felipe.Nascimento@multivalor.com.br> writes: 
let's imagine that my server resides in Time Zone(TZ) "-00", and one user
resides in TZ "-05". Let=B4s say that 12p.m. to the user is 3p.m. to the
server, on the same day.   
 
Let's say that the user inputs a date for a business meeting: "2002-06-20
12:00:00". The server will save "2002-06-20 12:00:00-00"??   
No, it won't.  If the user has TimeZone set to -05, as he should, then
his input of '2002-06-20 12:00:00' will be read as '2002-06-20 12:00:00-05'.
And it will be displayed to him that way.  But if someone else who
has TimeZone set differently looks at the stored value, it will be shown
to them properly converted into their timezone.
This can be a problem if the client is a web application in a fixed timezone and the person viewing the results is in a different time zone.

This all works exactly the same as ordinary Unix timekeeping --- in
essence, everything is GMT inside the system, and rotation into a
particular timezone happens on-the-fly when a timestamp value is
entered or displayed.  The TimeZone variable corresponds to the TZ
environment variable of Unix.
Another option is to get the timestamp via conversion to abstime to integer and your script or application format that timestamp (integer) to local format (string).
mytime::abstime::integer (this should be a GMT timestamp [int] if I remember correctly).

Someone correct me if I'm wrong.
 
My question is: how to manage this so I can send the email to the user at
his 11 o'clock (8 a.m. server time)???=20   
If you let the system do what it wants to do, it will do the right
thing.
		regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org 


Re: time stamp

От
Tom Lane
Дата:
Thomas Swan <tswan@idigx.com> writes:
>> No, it won't.  If the user has TimeZone set to -05, as he should, then
>> his input of '2002-06-20 12:00:00' will be read as '2002-06-20 12:00:00-05'.
>> And it will be displayed to him that way.  But if someone else who
>> has TimeZone set differently looks at the stored value, it will be shown
>> to them properly converted into their timezone.
>>
> This can be a problem if the client is a web application in a fixed
> timezone and the person viewing the results is in a different time zone.

If the webserver sets its session TimeZone according to the end user's
preference, everything will work just fine.  I am not sure how you
expect Postgres to intuit the proper timezone to use in this scenario
without any cooperation from the webserver...

            regards, tom lane

Re: time stamp

От
Thomas Swan
Дата:
Tom Lane wrote:
Thomas Swan <tswan@idigx.com> writes: 
No, it won't.  If the user has TimeZone set to -05, as he should, then
his input of '2002-06-20 12:00:00' will be read as '2002-06-20 12:00:00-05'.
And it will be displayed to him that way.  But if someone else who
has TimeZone set differently looks at the stored value, it will be shown
to them properly converted into their timezone.
     
This can be a problem if the client is a web application in a fixed 
timezone and the person viewing the results is in a different time zone.   
If the webserver sets its session TimeZone according to the end user's
preference, everything will work just fine.  I am not sure how you
expect Postgres to intuit the proper timezone to use in this scenario
without any cooperation from the webserver...
		regards, tom lane 
I don't expect it to be a postgres problem.  What I was referring to was the ability to design the software [not Postgres] to use the integer format of the timestamp field.  This way you don't have to know the local timezone of the server where the postgres client [in this case a PHP script called inside Apache] is.    Thus is if my the end user sets their timezone to be PST [through some preferences], then when I spit a formatted date string to them I can do the appropriate adjustments in my code [client] and present it according to their locale.   It's not the prettiest of solutions, but I found that it work with my situtation and thought it might help the person who was having the problems.   I don't see it as a fault of postgresql, never have.   If date transforms are needed that can be done just as effectively away from the database and the client database connectivity library.