Thread: Best way to convert PG's TIMESTAMPTZ to PHP DATE?
Anybody have any advice on this.. Michael
Both of these work OK for me
date('r',strtotime($record->date_field));
or
select to_char(date_field,'FORMAT SPEC') as date_field, column_two,
column_three from x where y=z
/B
----- Original Message -----
From: "Michael Hanna" <zen@hwcn.org>
To: <pgsql-php@postgresql.org>
Sent: Monday, June 30, 2003 12:54
Subject: [PHP] Best way to convert PG's TIMESTAMPTZ to PHP DATE?
> Anybody have any advice on this..
> Michael
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>
On Mon, Jun 30, 2003 at 03:54:22PM -0400, Michael Hanna wrote:
> Date: Mon, 30 Jun 2003 15:54:22 -0400
> Subject: [PHP] Best way to convert PG's TIMESTAMPTZ to PHP DATE?
> From: Michael Hanna <zen@hwcn.org>
> To: pgsql-php@postgresql.org
> X-Mailer: Apple Mail (2.552)
>
> Anybody have any advice on this..
> Michael
>
my solution in eros (which you can download for free from my website.. it is
licensed under the LGPL) is to store timestamps as timestamps (with
timezone), and in the SQL, I extract the UNIX epoch time, which I can then
manipulate with the strftime function. it works quite well, it's flexible,
and then you can use the built in indexing of pg without doing fancy tricks
or losing precision :)
for example:
eros=# \d gfile
Table "gfile"
Column | Type | Modifiers
----------------+--------------------------+--------------------------------------------------
id | integer | not null default nextval('"gfile_id_seq"'::text)
sigid | integer |
authorid | integer |
title | text |
summary | text |
body | text |
dateposted | timestamp with time zone |
lastmodified | timestamp with time zone |
lastmodifiedby | integer |
keywords | text |
Unique keys: gfile_id_key
notice the 'dateposted' and 'lastmodified' fields.
in my sql string, I do something like this:
select *, extract(epoch from dateposted) as datepostedepoch from gfile where
id=1;
then, to access the epoch based timestamp, I do something like this:
$lastmodified = $row["datepostedepoch"];
$lastmodified = datestamp($lastmodified);
print "{$lastmodified}";
the 'datestamp' function is a library routine I wrote (in php of course)
that accepts one parameter, applies it to a format string with strftime(),
and returns the result as a string.
that means that I can set up a format in the php, and if I want to change it
later, I don't have to modify all of my sql strings. I *used* to do it with
to_char, and that worked OK, but then when I wanted some other format for my
dates, I would have had to modify it *everywhere*. eventually I had set up a
define in common.php that would set the format string for to_char for me,
but the code looked messy.
I also wanted to make it so that each user, based on locality, could set
what format they want their times to be in. if I was using to_char, this
particular feature would be rather impractical (possible mind you, but
impractical).
also, since I wanted to make sure the user could do whatever they want with
the timestamps, I didn't want to have to point to seperate documentation
describing the usage of to_char.. strftime has been around a long time, and
is well documented.. there are multiple sources for the information, and
there's even a standard man page that shows what the usage is. most users
can leave the default alone (since it displays date, time with seconds, and
timezone formatted to the way *I* want to do things), but my way has enough
flexibility that you can change the format on the fly without a great deal
of fuss.
that has just been my experience.. not flaming, just offering my own
solution to the problem :)
hope that helps. if you have any questions about the technique, please feel
free to contact me directly, or via the list.
php.net documentation on strftime:
http://php.net/strftime
regards,
Jeff
--
|| Jeff - http://zoidtechnologies.com/
|| GNUPG Fingerprint: A607 0F19 7C75 1305 67E4 BDFF 26BD 606E 3517 2A42
On Mon, Jun 30, 2003 at 05:27:01PM -0400, Jeff wrote:
[..snipped..]
> $lastmodified = $row["datepostedepoch"];
> $lastmodified = datestamp($lastmodified);
> print "{$lastmodified}";
[..snipped..]
EEK! I should have proofread the *entire* message :(
that really should read:
$dateposted = $row["datepostedepoch"];
$dateposted = datestamp($dateposted);
print "{$dateposted}";
obviously that doesn't cover loading $row with the right values, but eros
handles that, too, for the most part. there are working examples in the
tarball of this technique in action.
regards,
Jeff
--
|| Jeff - http://zoidtechnologies.com/
|| GNUPG Fingerprint: A607 0F19 7C75 1305 67E4 BDFF 26BD 606E 3517 2A42
for some reason I get:
Wed, 31 Dec 1969 18:59:59 -0500
as output on any entry.
-----
$rows = pg_num_rows($result);
// if records present
if ($rows > 0)
{
// iterate through resultset
for ($i=0; $i<$rows; $i++)
{
$row = pg_fetch_object($result, $i);
$conv_date = date('r',strtotime($row->posted));
?>
<li><font size="-1"><b><? echo $conv_date; ?></b></font>
<br>
<font size="-1"><? echo $row->notes; ?></font>
<p>
<?
}
}
Hmmm,
Sometimes that happens to me too, I cannot seem to isolate what causes
it however. When I get that I use the PG to_char function in my statement
to convert the value to something I can use in PHP. I wish I could provide
more information about why this is hit and miss.
/B
----- Original Message -----
From: "Michael Hanna" <zen@hwcn.org>
To: "David Busby" <busby@pnts.com>
Cc: <pgsql-php@postgresql.org>
Sent: Monday, June 30, 2003 18:19
Subject: Re: [PHP] Best way to convert PG's TIMESTAMPTZ to PHP DATE?
for some reason I get:
Wed, 31 Dec 1969 18:59:59 -0500
as output on any entry.
-----
$rows = pg_num_rows($result);
// if records present
if ($rows > 0)
{
// iterate through resultset
for ($i=0; $i<$rows; $i++)
{
$row = pg_fetch_object($result, $i);
$conv_date = date('r',strtotime($row->posted));
?>
<li><font size="-1"><b><? echo $conv_date; ?></b></font>
<br>
<font size="-1"><? echo $row->notes; ?></font>
<p>
<?
}
}
On Monday, June 30, 2003, at 04:06 PM, David Busby wrote:
> Both of these work OK for me
>
> date('r',strtotime($record->date_field));
>
Oh and also that ugly time value is what happens when you try date('r',-1);
and the -1 comes from strtotime() not being able to convert the datetime
from postgres.
/B
----- Original Message -----
From: "Michael Hanna" <zen@hwcn.org>
To: "David Busby" <busby@pnts.com>
Cc: <pgsql-php@postgresql.org>
Sent: Monday, June 30, 2003 18:19
Subject: Re: [PHP] Best way to convert PG's TIMESTAMPTZ to PHP DATE?
for some reason I get:
Wed, 31 Dec 1969 18:59:59 -0500
as output on any entry.
-----
$rows = pg_num_rows($result);
// if records present
if ($rows > 0)
{
// iterate through resultset
for ($i=0; $i<$rows; $i++)
{
$row = pg_fetch_object($result, $i);
$conv_date = date('r',strtotime($row->posted));
?>
<li><font size="-1"><b><? echo $conv_date; ?></b></font>
<br>
<font size="-1"><? echo $row->notes; ?></font>
<p>
<?
}
}
On Monday, June 30, 2003, at 04:06 PM, David Busby wrote:
> Both of these work OK for me
>
> date('r',strtotime($record->date_field));
>
Yep, use sscanf function of PHP. Chris ----- Original Message ----- From: "Michael Hanna" <zen@hwcn.org> To: <pgsql-php@postgresql.org> Sent: Tuesday, July 01, 2003 3:54 AM Subject: [PHP] Best way to convert PG's TIMESTAMPTZ to PHP DATE? > Anybody have any advice on this.. > Michael > > > ---------------------------(end of broadcast)--------------------------- > TIP 8: explain analyze is your friend >
Not sure why this results in nothing: $query2 = "SELECT to_char(posted, 'Day, DD HH12:MI:SS') FROM healthnotes";