Re: definative way to place secs from epoc into timestamp

On Thu, 2005-03-03 at 14:58, Andrew - Supernews wrote:
> On 2005-03-03, Bret Hughes <bhughes(at)elevating(dot)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(at)sonecdm bhughes]$ psql elevating -c '\d testtime'
Table "public.testtime"
Column | Type | Modifiers
--------+-----------------------------+-----------
ts | timestamp without time zone |

The script:

[bhughes(at)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(at)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