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
