From: Bret Hughes <bhughes@elevating.com>
To: postgresql sql list <pgsql-sql@postgresql.org>
Subject: Re: definative way to place secs from epoc into timestamp
Date: 03 Mar 2005 23:47:10 -0600
Message-ID: <1109915231.28180.182.camel@bretsony> (raw)
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: bhughes@elevating.com
Subject: Re: definative way to place secs from epoc into timestamp
In-Reply-To: <1109915231.28180.182.camel@bretsony>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox