Thread: definative way to place secs from epoc into timestamp column
I give up. I have STFW and STFM and still do not feel like I have a good way to update/insert into a timestamp w/o TZ column with an integer representing seconds from epoch. I am adding functionality to a php app that does a fair amount of work with time and am currently using abstime($timestamp). $timestamp is a php timestamp. I found this on the web somewhere since I find no reference to a function abstime. There is significant hits relating to abstime in the docs but it all seems to refer to an internal data type of low res time data. Help. I would really like to do this in the most efficient way possible but would like it be not likely to break in future releases. Bret
Bret Hughes <bhughes@elevating.com> writes:
> I give up. I have STFW and STFM and still do not feel like I have a
> good way to update/insert into a timestamp w/o TZ column with an integer
> representing seconds from epoch.
The docs say:
Here is how you can convert an epoch value back to a time stamp:
SELECT TIMESTAMP WITH TIME ZONE 'epoch' + 982384720 * INTERVAL '1 second';
If you want a timestamp w/o time zone then the right thing depends on
what you think the reference epoch is. If you do
SELECT TIMESTAMP WITHOUT TIME ZONE 'epoch' + 982384720 * INTERVAL '1 second';
then what you will get is the correct equivalent of the Unix timestamp
in GMT time. If you do the first calculation and then cast to timestamp
w/o time zone then what you will get is a correct equivalent in your
TimeZone setting. For instance
regression=# show timezone;TimeZone
----------EST5EDT
(1 row)
regression=# SELECT TIMESTAMP WITH TIME ZONE 'epoch' + 982384720 * INTERVAL '1 second'; ?column?
------------------------2001-02-16 23:38:40-05
(1 row)
regression=# SELECT TIMESTAMP WITHOUT TIME ZONE 'epoch' + 982384720 * INTERVAL '1 second'; ?column?
---------------------2001-02-17 04:38:40
(1 row)
regression=# SELECT (TIMESTAMP WITH TIME ZONE 'epoch' + 982384720 * INTERVAL '1 second')::timestamp without time zone;
timestamp
---------------------2001-02-16 23:38:40
(1 row)
regards, tom lane
On Wed, 2005-03-02 at 13:52, Tom Lane wrote: > Bret Hughes <bhughes@elevating.com> writes: > > I give up. I have STFW and STFM and still do not feel like I have a > > good way to update/insert into a timestamp w/o TZ column with an integer > > representing seconds from epoch. > > The docs say: > > Here is how you can convert an epoch value back to a time stamp: > > SELECT TIMESTAMP WITH TIME ZONE 'epoch' + 982384720 * INTERVAL '1 second'; > > If you want a timestamp w/o time zone then the right thing depends on > what you think the reference epoch is. If you do > > SELECT TIMESTAMP WITHOUT TIME ZONE 'epoch' + 982384720 * INTERVAL '1 second'; > > then what you will get is the correct equivalent of the Unix timestamp > in GMT time. If you do the first calculation and then cast to timestamp > w/o time zone then what you will get is a correct equivalent in your > TimeZone setting. For instance Thanks for the feed back tom I say that but I could not believe that I have to jump through all those hoops on an insert or update update mytable set (lasttime =(SELECT TIMESTAMP WITH TIME ZONE 'epoch' + 982384720 * INTERVAL '1 second') ) is this what you are saying I need to do? also, what is happening with abstime(982384720)? this works as expected (by me ). Is this a bad idea? I can't believe that all the complicated string manipulation stuff is there but I have to run a subselect to insert a numeric value that I suspect is close to how it is stored anyway. Of course the last part is a WAG. Bret
Bret Hughes <bhughes@elevating.com> writes:
> Thanks for the feed back tom I say that but I could not believe that I
> have to jump through all those hoops on an insert or update
> update mytable set (lasttime =(SELECT TIMESTAMP WITH TIME ZONE 'epoch' +
> 982384720 * INTERVAL '1 second') )
> is this what you are saying I need to do?
You can make a function that embodies whichever semantics you want.
> also, what is happening with abstime(982384720)? this works as expected
> (by me ). Is this a bad idea?
It won't be there forever.
regards, tom lane
On Wed, 2005-03-02 at 14:26, Tom Lane wrote: > Bret Hughes <bhughes@elevating.com> writes: > > Thanks for the feed back tom I say that but I could not believe that I > > have to jump through all those hoops on an insert or update > > > update mytable set (lasttime =(SELECT TIMESTAMP WITH TIME ZONE 'epoch' + > > 982384720 * INTERVAL '1 second') ) > > > is this what you are saying I need to do? > > You can make a function that embodies whichever semantics you want. > > > also, what is happening with abstime(982384720)? this works as expected > > (by me ). Is this a bad idea? > > It won't be there forever. > Thanks again for the help Tom. My solution for those intrepid archive searchers that follow: also my first two pgsql functions :) cat ts2int.sql drop function int2ts(integer); drop function ts2int(timestamp without time zone); create function int2ts(integer) returns timestamp as ' SELECT ( TIMESTAMP WITH TIME ZONE \'epoch\' + $1 * INTERVAL \'1 second\')::timestamp without time zone; ' language sql; create function ts2int(timestamp without time zone) returns int as ' select extract( \'epoch\' from $1)::integer; ' language sql; comment on function int2ts(integer) is 'convert a unix timestamp based integer to a timestamp without time zone'; comment on function ts2int(timestamp without time zone) is 'convert a timstamp without time zone to a unix timstamp based integer'; Thanks again for your patience as I try to get my head around how pg handles this stuff. I am getting close to getting my head around it but seem to have a block on picturing the internals. Bret
Bret Hughes <bhughes@elevating.com> writes:
> create function int2ts(integer) returns timestamp as '
> SELECT ( TIMESTAMP WITH TIME ZONE \'epoch\' + $1 * INTERVAL \'1
> second\')::timestamp without time zone;
> ' language sql;
> create function ts2int(timestamp without time zone) returns int as '
> select extract( \'epoch\' from $1)::integer;
> ' language sql;
Looks good as far as it goes. Two thoughts:
* both functions should probably be marked STRICT STABLE
(not IMMUTABLE, because they depend on the local timezone setting).
* have you considered allowing the numeric values to be float8 instead
of integer? There doesn't seem any reason to disallow sub-second
precision.
regards, tom lane
On Mar 3, 2005, at 14:42, Bret Hughes wrote: > also my first two pgsql functions :) > > cat ts2int.sql FWIW, there's a patch in the queue for 8.1 that adds a to_timestamp function that converts between Unix epoch and timestamp with time zone. http://momjian.postgresql.org/cgi-bin/pgpatches2 Doesn't help you now, but it'll be there in the future. Michael Glaesemann grzm myrealbox com
On Thu, 2005-03-03 at 00:25, Tom Lane wrote: > Bret Hughes <bhughes@elevating.com> writes: > > create function int2ts(integer) returns timestamp as ' > > SELECT ( TIMESTAMP WITH TIME ZONE \'epoch\' + $1 * INTERVAL \'1 > > second\')::timestamp without time zone; > > ' language sql; > > > create function ts2int(timestamp without time zone) returns int as ' > > select extract( \'epoch\' from $1)::integer; > > ' language sql; > > Looks good as far as it goes. Two thoughts: > > * both functions should probably be marked STRICT STABLE > (not IMMUTABLE, because they depend on the local timezone setting). > > * have you considered allowing the numeric values to be float8 instead > of integer? There doesn't seem any reason to disallow sub-second > precision. > well no I had not considered it but am now :) I need to read some more as to the STRICT STABLE stuff, thanks for the tips. Bret
On Thu, 2005-03-03 at 00:41, Michael Glaesemann wrote: > > On Mar 3, 2005, at 14:42, Bret Hughes wrote: > > > also my first two pgsql functions :) > > > > cat ts2int.sql > > FWIW, there's a patch in the queue for 8.1 that adds a to_timestamp > function that converts between Unix epoch and timestamp with time zone. > > http://momjian.postgresql.org/cgi-bin/pgpatches2 > > Doesn't help you now, but it'll be there in the future. > Cool. Nice to know I am not the only one. a RFE would be to let to_timestamp be to a timezone without time zone and have a to_timestamptz do the time zone thing. Seems more consistent and would give me the functionality I am looking for :) Bret
On 2005-03-03, Bret Hughes <bhughes@elevating.com> wrote: > a RFE would be to let to_timestamp be to a timezone without time zone > and have a to_timestamptz do the time zone thing. Seems more consistent > and would give me the functionality I am looking for :) Unix epoch times correspond to timestamp _with_ time zone. (Why are you using timestamp without time zone anyway? For recording the time at which an event occurred that usage is simply wrong - in fact I can't see any situation in which a Unix epoch time can correctly be converted to a timestamp without time zone.) -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services
On Thu, 2005-03-03 at 14:58, Andrew - Supernews wrote:
> On 2005-03-03, Bret Hughes <bhughes@elevating.com> wrote:
> > a RFE would be to let to_timestamp be to a timezone without time zone
> > and have a to_timestamptz do the time zone thing. Seems more consistent
> > and would give me the functionality I am looking for :)
>
> Unix epoch times correspond to timestamp _with_ time zone.
>
> (Why are you using timestamp without time zone anyway? For recording the
> time at which an event occurred that usage is simply wrong - in fact I
> can't see any situation in which a Unix epoch time can correctly be
> converted to a timestamp without time zone.)
>
Valid question. Because there is no reason to keep up with time zones
and the fact that I want the same value from the data base that I put
into it. The app that this db supports is written in php and I kept
getting something different out than what I put into it in the other
passes I made while trying to get my head around this. the timestamps
have historically been stored in flat files.
here is an example of a valid use:
The table:
[bhughes@sonecdm bhughes]$ psql elevating -c '\d testtime' Table "public.testtime"Column | Type
| Modifiers
--------+-----------------------------+-----------ts | timestamp without time zone |
The script:
[bhughes@sonecdm elevatetest]$ cat timetest.php
#!/usr/bin/php -q
<?php
include ('environment.inc');
include ('elefunctions.php');
$dbconn = ele_db_connect();
print "date from date command\n";
print `date` ;
print "system secs " . `date +%s`;
$timevar = mktime();
print "php time secs $timevar\n";
print strftime('%D %H:%M', $timevar) . "\n";
$query = "insert into testtime values (int2ts($timevar))";
$result = pg_query($dbconn, $query);
if (! $result) { print "$query \n"; die ('No result ' . pg_last_error($dbconn) . "\n");
}
print "the number of rows affected was " . pg_affected_rows($result) .
"\n";
$result = pg_query($dbconn, 'select ts, ts2int(ts) from testtime order
by
ts DESC ; ');
$timearr = pg_fetch_array($result);
print_r($timearr);
?>
The output:
[bhughes@sonecdm elevatetest]$ ./timetest.php
date from date command
Thu Mar 3 22:30:14 EST 2005
system secs 1109907014
php time secs 1109907014
03/03/05 22:30
the number of rows affected was 1
Array
( [0] => 2005-03-03 22:30:14 [ts] => 2005-03-03 22:30:14 [1] => 1109907014 [ts2int] => 1109907014
)
What goes in comes out. Gotta like it.
Bret
On 2005-03-04, Bret Hughes <bhughes@elevating.com> wrote: >> Unix epoch times correspond to timestamp _with_ time zone. >> >> (Why are you using timestamp without time zone anyway? For recording the >> time at which an event occurred that usage is simply wrong - in fact I >> can't see any situation in which a Unix epoch time can correctly be >> converted to a timestamp without time zone.) > > Valid question. Because there is no reason to keep up with time zones It's a common mistake to think that just because you don't need to keep track of time zones that somehow using timestamp without time zone is correct. It is _not_. "timestamp with time zone" and "timestamp without time zone" have _very_ different semantics. One way to look at it is that "timestamp with time zone" designates a specific instant in absolute time (past or future). It is therefore the correct type to use for recording when something happened. In contrast, "timestamp without time zone" designates a point on the calendar, which has a different meaning according to where you are, and when. So the latter type crops up in some cases in calendar applications, and also in input/output conversions, but it's more often than not the _wrong_ type to use for storage, since the meaning changes with the timezone (and data _does_ get moved across timezones, whether due to physical relocation or other factors). Unix epoch times have the same semantics as "timestamp with time zone". > and the fact that I want the same value from the data base that I put > into it. "same" in which sense? The same absolute point in time? Or the same point on a calendar? Obviously if the timezone doesn't change, then the two are equivalent; but which one is your application actually looking for? (If your app is using Unix epoch times, then it's looking only at the absolute time and not the calendar time...) Here's an example of how it breaks (using your own conversion functions): test=> set timezone to 'UTC'; SET test=> insert into ttst values (int2ts(1109916954)); INSERT 887766166 1 test=> select ts,ts2int(ts) from ttst; ts | ts2int ---------------------+------------2005-03-04 06:15:54 | 1109916954 (1 row) (that is the correct UTC time corresponding to 1109916954) test=> set timezone to 'America/Denver'; SET test=> select ts,ts2int(ts) from ttst; ts | ts2int ---------------------+------------2005-03-04 06:15:54 | 1109942154 (1 row) test=> set timezone to 'America/New_York'; SET test=> select ts,ts2int(ts) from ttst; ts | ts2int ---------------------+------------2005-03-04 06:15:54 | 1109934954 (1 row) Notice the value stored in the DB didn't change, but it suddenly means something different... In contrast, if you do the same thing with "timestamp with time zone", then the Unix time that you get back will _always_ be the same, as you would expect, regardless of the time zone. Using functions identical to yours except using "with time zone": test=> insert into tztst values (int2tsz(1109916954)); INSERT 889130554 1 test=> select ts,ts2int(ts) from tztst; ts | ts2int ------------------------+------------2005-03-04 06:15:54+00 | 1109916954 (1 row) test=> set timezone to 'America/New_York'; SET test=> select ts,ts2int(ts) from tztst; ts | ts2int ------------------------+------------2005-03-04 01:15:54-05 | 1109916954 (1 row) test=> set timezone to 'America/Los_Angeles'; SET test=> select ts,ts2int(ts) from tztst; ts | ts2int ------------------------+------------2005-03-03 22:15:54-08 | 1109916954 (1 row) Notice that the stored timestamp doesn't actually change; it is displayed differently according to the timezone. The Unix time correctly _doesn't_ change, reflecting the fact that what we stored was the absolute time. -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services
On Mar 4, 2005, at 14:47, Bret Hughes wrote:
> On Thu, 2005-03-03 at 14:58, Andrew - Supernews wrote:
>> (Why are you using timestamp without time zone anyway? For recording
>> the
>> time at which an event occurred that usage is simply wrong - in fact I
>> can't see any situation in which a Unix epoch time can correctly be
>> converted to a timestamp without time zone.)
>>
>
> Valid question. Because there is no reason to keep up with time zones
> and the fact that I want the same value from the data base that I put
> into it. The app that this db supports is written in php and I kept
> getting something different out than what I put into it in the other
> passes I made while trying to get my head around this. the timestamps
> have historically been stored in flat files.
<snip />
> What goes in comes out. Gotta like it.
I think the reason this works is because your webserver and your
postgresql server are in the same time zone, which is probably an
assumption made in a great-many cases. You may run into problems if at
some time the dbms and webserver are not in the same time zone and
you're relying on dbms-generated times (such as now() or
current_timestamp), or if the system
is relocated to another time zone.
I think the following illustrates a problem that can occur if the
assumption that the time zone is not constant is no longer valid.
Your system is working for you, so that's great. I just wanted to
explore this for myself a bit more -- I find the time zone related
material hard to get my head around myself :). Since I went through it,
I thought I'd share it with the list.
Regards,
Michael Glaesemann
grzm myrealbox com
test=# create table ts2int (ts2int_id serial not null unique , ts timestamp without time zone default
current_timestamp , tstz timestamptz default current_timestamp) without oids;
NOTICE: CREATE TABLE will create implicit sequence
"ts2int_ts2int_id_seq" for serial column "ts2int.ts2int_id"
NOTICE: CREATE TABLE / UNIQUE will create implicit index
"ts2int_ts2int_id_key" for table "ts2int"
CREATE TABLE
test=# \d ts2int Table "public.ts2int" Column | Type
|
Modifiers
-----------+-----------------------------
+--------------------------------------------------------------- ts2int_id | integer | not null
default
nextval('public.ts2int_ts2int_id_seq'::text) ts | timestamp without time zone | default
('now'::text)::timestamp(6) with time zone tstz | timestamp with time zone | default
('now'::text)::timestamp(6) with time zone
Indexes: "ts2int_ts2int_id_key" UNIQUE, btree (ts2int_id)
test=# insert into ts2int (ts) values (default);
INSERT 0 1
To simulate webserver and postgresql server being in different time
zones, I'm
using the "at time zone" construct to convert to CST.
test=# insert into ts2int (ts) values (current_timestamp at time zone
'CST');
INSERT 0 1
test=# select * from ts2int; ts2int_id | ts | tstz
-----------+----------------------------+------------------------------- 1 | 2005-03-04 15:46:20.443158 |
2005-03-0415:46:20.443158+09 2 | 2005-03-04 00:46:50.336831 | 2005-03-04 15:46:50.336831+09
(2 rows)
test=# select ts2int_id , extract('epoch' from ts) as ts_epoch , extract ('epoch' from tstz) as tstz_epoch
fromts2int; ts2int_id | ts_epoch | tstz_epoch
-----------+------------------+------------------ 1 | 1109918780.44316 | 1109918780.44316 2 |
1109864810.33683| 1109918810.33683
(2 rows)
Note that ts_epoch and tstz_epoch are the same for 1, but different for
2. Both
ts and tstz are being evaluated at +9 (the postgres server time zone
offset). As
ts for 2 wasn't inserted at +9, it's not the same.
test=# select ts2int_id , extract('epoch' from ts at time zone 'CST') as ts_epoch , extract ('epoch' from tstz)
aststz_epoch from ts2int; ts2int_id | ts_epoch | tstz_epoch
-----------+------------------+------------------ 1 | 1109972780.44316 | 1109918780.44316 2 |
1109918810.33683| 1109918810.33683
(2 rows)
Note that ts_epoch and tstz_epoch are the same for 2, but different for
1. ts
was inserted relative to CST and is now being evaluated "at time zone
'CST'", so
the ts and tstz values for 2 are "the same". ts_epoch for 2 is also the
Unix timestamp for the time that was originally inserted.
test=# select ts2int_id , extract('epoch' from ts) as ts_epoch , extract ('epoch' from tstz at time zone 'CST')
aststz_epoch from ts2int; ts2int_id | ts_epoch | tstz_epoch
-----------+------------------+------------------ 1 | 1109918780.44316 | 1109864780.44316 2 |
1109864810.33683| 1109864810.33683
(2 rows)
Note again that ts_epoch and tstz_epoch are the same for 2, but
different for 1. ts is
being evaluated at +9, while tstz is being converted to CST before
extracting the epoch. However, both ts_epoch and tstz_epoch for 2 are
not the Unix timestamps for the timestamps that were originally
inserted. Actually, none of them are.
And finally, for completeness:
test=# select ts2int_id , extract('epoch' from ts at time zone 'CST') as ts_epoch , extract ('epoch' from tstz at
timezone 'CST') as tstz_epoch from ts2int; ts2int_id | ts_epoch | tstz_epoch
-----------+------------------+------------------ 1 | 1109972780.44316 | 1109864780.44316 2 |
1109918810.33683| 1109864810.33683
(2 rows)
Now everything's pretty screwed up. ts_epoch for 2 is actually
returning the "proper answer" (meaning the epoch for the time that was
originally inserted), but it's hard to tell as everything else is
out-of-whack.
Just so I don't make a newb mistake I should use timestamptz not timestamp where the exact moment is important? My conversion which is not live yet is using timestamp as I did not clearly understand (but be very easy I hope to modify in my app that creates and moves the data just use timestamptz instead of timestamp). Joel Fradkin Wazagua, Inc. 2520 Trailmate Dr Sarasota, Florida 34243 Tel. 941-753-7111 ext 305 jfradkin@wazagua.com www.wazagua.com Powered by Wazagua Providing you with the latest Web-based technology & advanced tools. C 2004. WAZAGUA, Inc. All rights reserved. WAZAGUA, IncThis email message is for the use of the intended recipient(s) andmay contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and delete and destroy all copies of the original message, including attachments. -----Original Message----- From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Andrew - Supernews Sent: Friday, March 04, 2005 2:15 AM To: pgsql-sql@postgresql.org Subject: Re: [SQL] definative way to place secs from epoc into timestamp On 2005-03-04, Bret Hughes <bhughes@elevating.com> wrote: >> Unix epoch times correspond to timestamp _with_ time zone. >> >> (Why are you using timestamp without time zone anyway? For recording the >> time at which an event occurred that usage is simply wrong - in fact I >> can't see any situation in which a Unix epoch time can correctly be >> converted to a timestamp without time zone.) > > Valid question. Because there is no reason to keep up with time zones It's a common mistake to think that just because you don't need to keep track of time zones that somehow using timestamp without time zone is correct. It is _not_. "timestamp with time zone" and "timestamp without time zone" have _very_ different semantics. One way to look at it is that "timestamp with time zone" designates a specific instant in absolute time (past or future). It is therefore the correct type to use for recording when something happened. In contrast, "timestamp without time zone" designates a point on the calendar, which has a different meaning according to where you are, and when. So the latter type crops up in some cases in calendar applications, and also in input/output conversions, but it's more often than not the _wrong_ type to use for storage, since the meaning changes with the timezone (and data _does_ get moved across timezones, whether due to physical relocation or other factors). Unix epoch times have the same semantics as "timestamp with time zone". > and the fact that I want the same value from the data base that I put > into it. "same" in which sense? The same absolute point in time? Or the same point on a calendar? Obviously if the timezone doesn't change, then the two are equivalent; but which one is your application actually looking for? (If your app is using Unix epoch times, then it's looking only at the absolute time and not the calendar time...) Here's an example of how it breaks (using your own conversion functions): test=> set timezone to 'UTC'; SET test=> insert into ttst values (int2ts(1109916954)); INSERT 887766166 1 test=> select ts,ts2int(ts) from ttst; ts | ts2int ---------------------+------------2005-03-04 06:15:54 | 1109916954 (1 row) (that is the correct UTC time corresponding to 1109916954) test=> set timezone to 'America/Denver'; SET test=> select ts,ts2int(ts) from ttst; ts | ts2int ---------------------+------------2005-03-04 06:15:54 | 1109942154 (1 row) test=> set timezone to 'America/New_York'; SET test=> select ts,ts2int(ts) from ttst; ts | ts2int ---------------------+------------2005-03-04 06:15:54 | 1109934954 (1 row) Notice the value stored in the DB didn't change, but it suddenly means something different... In contrast, if you do the same thing with "timestamp with time zone", then the Unix time that you get back will _always_ be the same, as you would expect, regardless of the time zone. Using functions identical to yours except using "with time zone": test=> insert into tztst values (int2tsz(1109916954)); INSERT 889130554 1 test=> select ts,ts2int(ts) from tztst; ts | ts2int ------------------------+------------2005-03-04 06:15:54+00 | 1109916954 (1 row) test=> set timezone to 'America/New_York'; SET test=> select ts,ts2int(ts) from tztst; ts | ts2int ------------------------+------------2005-03-04 01:15:54-05 | 1109916954 (1 row) test=> set timezone to 'America/Los_Angeles'; SET test=> select ts,ts2int(ts) from tztst; ts | ts2int ------------------------+------------2005-03-03 22:15:54-08 | 1109916954 (1 row) Notice that the stored timestamp doesn't actually change; it is displayed differently according to the timezone. The Unix time correctly _doesn't_ change, reflecting the fact that what we stored was the absolute time. -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
Andrew - Supernews <andrew+nonews@supernews.com> writes:
> On 2005-03-04, Bret Hughes <bhughes@elevating.com> wrote:
>>> (Why are you using timestamp without time zone anyway? For recording the
>>
>> Valid question. Because there is no reason to keep up with time zones
> It's a common mistake to think that just because you don't need to keep
> track of time zones that somehow using timestamp without time zone is
> correct. It is _not_. "timestamp with time zone" and "timestamp without
> time zone" have _very_ different semantics.
> [ excellent example snipped ]
It's curious that people who say they want Unix timestamps find it so
hard to wrap their heads around this, because in fact "timestamp with
time zone" operates EXACTLY the way that Unix timekeeping is done.
Consider this:
$ export TZ=GMT$ dateFri Mar 4 15:11:31 GMT 2005$ export TZ=EST5EDT$ dateFri Mar 4 10:11:35 EST 2005
The system's internal idea of the time didn't change (modulo the few
seconds it took to type the commands), but the way it is displayed
changed.
regards, tom lane
>>and the fact that I want the same value from the data base that I put >>into it. > > > "same" in which sense? The same absolute point in time? Or the same point > on a calendar? Obviously if the timezone doesn't change, then the two are > equivalent; but which one is your application actually looking for? (If > your app is using Unix epoch times, then it's looking only at the absolute > time and not the calendar time...) > Unix time stamps, short (int) or long res, are always supposed to GMT based, as far as I know - I never seen anything different, except maybe in homebrew software. So it should be both calendar and P.I.T. And you wouldn't need the TZ storage if the date-number and number-> translation itself takes the TZ arg so that it can localize the Human String for you. Ken
> > Unix time stamps, short (int) or long res, are always supposed to GMT > based, as far as I know - I never seen anything different, except maybe > in homebrew software. So it should be both calendar and P.I.T. And you > wouldn't need the TZ storage if the date-number and number-> translation > itself takes the TZ arg so that it can localize the Human String for you. > > Ken > In fact, I would suggest that if there is any function, or field, that takes a TZ-less argument (*especially* if it takes only the number), that its name should be made to contain 'UTC' so clearly disambiguate whats its intended use for (since zone-less values/fields SHOULD be regarded as UTC) - Otherwise, some users will place epoch numbers adjusted for the their timezone in the field (and even with daylight saving offsets applies, somewhat amusingly but wrong). So then two different users are using the exact same datatype for inconsistent types. (just a concern for interoperability, user awareness, and when an employee comes on-board and has to deal with bad legacy)
On 2005-03-04, "Joel Fradkin" <jfradkin@wazagua.com> wrote: > Just so I don't make a newb mistake I should use timestamptz not timestamp > where the exact moment is important? Yes. -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services
On Fri, 2005-03-04 at 01:35, Michael Glaesemann wrote: > > On Mar 4, 2005, at 14:47, Bret Hughes wrote: > > > On Thu, 2005-03-03 at 14:58, Andrew - Supernews wrote: > >> (Why are you using timestamp without time zone anyway? For recording > >> the > >> time at which an event occurred that usage is simply wrong - in fact I > >> can't see any situation in which a Unix epoch time can correctly be > >> converted to a timestamp without time zone.) > >> > > > > Valid question. Because there is no reason to keep up with time zones > > and the fact that I want the same value from the data base that I put > > into it. The app that this db supports is written in php and I kept > > getting something different out than what I put into it in the other > > passes I made while trying to get my head around this. the timestamps > > have historically been stored in flat files. > > <snip /> > > > What goes in comes out. Gotta like it. > > I think the reason this works is because your webserver and your > postgresql server are in the same time zone, which is probably an > assumption made in a great-many cases. You may run into problems if at > some time the dbms and webserver are not in the same time zone and > you're relying on dbms-generated times (such as now() or > current_timestamp), or if the system > is relocated to another time zone. > > I think the following illustrates a problem that can occur if the > assumption that the time zone is not constant is no longer valid. > > Your system is working for you, so that's great. I just wanted to > explore this for myself a bit more -- I find the time zone related > material hard to get my head around myself :). Since I went through it, > I thought I'd share it with the list. > Thanks for the additional walk through. Thanks also to everyone else who has contributed to this thread and my education. I think I finally figured out what is what. Part of my issue has been that there are so many things that can affect the tz offset that is retrieved from the os via php or some other language I was looking to eliminate one of them. Examples that "stayed" in psql were not helping me on that point. Once I realized that the simple solution was indeed to stay in UTC (using gmmktime/gmstrftime rather than mktime/strftime in php for instance) and everyone's constructive criticism finally hammered the point. I have been humbled by this which my wife will tell you is not so bad a thing. I usually "get it" pretty quickly when tackling new concepts but this whole deal took me much longer than usual. I am working to alter the design now and since most of the db calls involving timestamps are contained in two php classes I should have it fixed this week end. Thanks again for everyone's patience and help. Bret