Subject: Re: definative way to place secs from epoc into timestamp
From: Bret Hughes <bhughes@elevating.com>
To: postgresql sql list <pgsql-sql@postgresql.org>
In-Reply-To: <slrnd2euj5.29om.andrew+nonews@trinity.supernews.net>
References: <1109780719.28180.86.camel@bretsony>
	<13089.1109793126@sss.pgh.pa.us> <1109794066.27857.100.camel@bretsony>
	<13466.1109795186@sss.pgh.pa.us> <1109828560.27905.137.camel@bretsony>
	<2bf00b6e6300e2c76bc3c09fa89e7913@myrealbox.com>
	<1109834845.27905.147.camel@bretsony>
	<slrnd2euj5.29om.andrew+nonews@trinity.supernews.net>
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Date: 03 Mar 2005 23:47:10 -0600
Message-Id: <1109915231.28180.182.camel@bretsony>
Mime-Version: 1.0

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