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

Re: Best way to convert PG"s TIMESTAMPTZ to PHP DATE?

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"
To:
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
>


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Re: Best way to convert PG"s TIMESTAMPTZ to PHP DATE?

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
> 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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Re: Best way to convert PG"s TIMESTAMPTZ to PHP DATE?

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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Re: 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 =3D pg_num_rows($result);

// if records present
if ($rows > 0)
{
// iterate through resultset
for ($i=3D0; $i<$rows; $i++)
{
$row =3D pg_fetch_object($result, $i);
$conv_date =3D date('r',strtotime($row->posted));
?>

Re: Best way to convert PG"s TIMESTAMPTZ to PHP DATE?

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"
To: "David Busby"
Cc:
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));
?>

Re: Best way to convert PG"s TIMESTAMPTZ to PHP DATE?

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"
To: "David Busby"
Cc:
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));
?>

Re: Best way to convert PG"s TIMESTAMPTZ to PHP DATE?

Yep, use sscanf function of PHP.

Chris

----- Original Message -----
From: "Michael Hanna"
To:
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
>

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Re: Best way to convert PG"s TIMESTAMPTZ to PHP DATE?

Not sure why this results in nothing:

$query2 =3D "SELECT to_char(posted, 'Day, DD HH12:MI:SS') FROM=20
healthnotes";

Also, there are multiple postings per day, how do I SELECT only one?=20
DISTINCT? Not sure how to do this..

Finally, if I ever get the output, would nested for-loops properly=20
output the information such that for each date printed, it will print=20
the multiple items on that day?

Michael

On Monday, June 30, 2003, at 09:42 PM, David Busby wrote:

> Hmmm,
> Sometimes that happens to me too, I cannot seem to isolate what=20
> causes
> it however. When I get that I use the PG to_char function in my=20
> statement
> to convert the value to something I can use in PHP. I wish I could=20
> provide
> more information about why this is hit and miss.
>
> /B
>
>
> ----- Original Message -----
> From: "Michael Hanna"
> To: "David Busby"
> Cc:
> 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 =3D pg_num_rows($result);
>
> // if records present
> if ($rows > 0)
> {
> // iterate through resultset
> for ($i=3D0; $i<$rows; $i++)
> {
> $row =3D pg_fetch_object($result, $i);
> $conv_date =3D date('r',strtotime($row->posted));
> ?>
>