Re: Best way to convert PG's TIMESTAMPTZ to PHP DATE? - Mailing list pgsql-php

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