Обсуждение: 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'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"
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 >
> 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 -
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 >
"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
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
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
-
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
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
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
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
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.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.
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).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.
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)???=20If 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
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.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