pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Best way to convert PG's TIMESTAMPTZ to PHP DATE?
9+ messages / 4 participants
[nested] [flat]

* Best way to convert PG's TIMESTAMPTZ to PHP DATE?
@ 2003-06-30 19:54 Michael Hanna <zen@hwcn.org>
  2003-06-30 20:06 ` Re: Best way to convert PG's TIMESTAMPTZ to PHP DATE? David Busby <busby@pnts.com>
  2003-06-30 21:27 ` Re: Best way to convert PG's TIMESTAMPTZ to PHP DATE? Jeff <jam@zoidtechnologies.com>
  2003-07-01 02:02 ` Re: Best way to convert PG's TIMESTAMPTZ to PHP DATE? Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  0 siblings, 3 replies; 9+ messages in thread

From: Michael Hanna @ 2003-06-30 19:54 UTC (permalink / raw)
  To: pgsql-php

Anybody have any advice on this..
Michael




^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Best way to convert PG's TIMESTAMPTZ to PHP DATE?
  2003-06-30 19:54 Best way to convert PG's TIMESTAMPTZ to PHP DATE? Michael Hanna <zen@hwcn.org>
@ 2003-06-30 20:06 ` David Busby <busby@pnts.com>
  2003-07-01 01:19   ` Re: Best way to convert PG's TIMESTAMPTZ to PHP DATE? Michael Hanna <zen@hwcn.org>
  2 siblings, 1 reply; 9+ messages in thread

From: David Busby @ 2003-06-30 20:06 UTC (permalink / raw)
  To: Michael Hanna <zen@hwcn.org>; pgsql-php

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
>




^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Best way to convert PG's TIMESTAMPTZ to PHP DATE?
  2003-06-30 19:54 Best way to convert PG's TIMESTAMPTZ to PHP DATE? Michael Hanna <zen@hwcn.org>
  2003-06-30 20:06 ` Re: Best way to convert PG's TIMESTAMPTZ to PHP DATE? David Busby <busby@pnts.com>
@ 2003-07-01 01:19   ` Michael Hanna <zen@hwcn.org>
  2003-07-01 01:42     ` Re: Best way to convert PG's TIMESTAMPTZ to PHP DATE? David Busby <busby@pnts.com>
  2003-07-01 01:43     ` Re: Best way to convert PG's TIMESTAMPTZ to PHP DATE? David Busby <busby@pnts.com>
  0 siblings, 2 replies; 9+ messages in thread

From: Michael Hanna @ 2003-07-01 01:19 UTC (permalink / raw)
  To: David Busby <busby@pnts.com>; +Cc: pgsql-php

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




^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Best way to convert PG's TIMESTAMPTZ to PHP DATE?
  2003-06-30 19:54 Best way to convert PG's TIMESTAMPTZ to PHP DATE? Michael Hanna <zen@hwcn.org>
  2003-06-30 20:06 ` Re: Best way to convert PG's TIMESTAMPTZ to PHP DATE? David Busby <busby@pnts.com>
  2003-07-01 01:19   ` Re: Best way to convert PG's TIMESTAMPTZ to PHP DATE? Michael Hanna <zen@hwcn.org>
@ 2003-07-01 01:42     ` David Busby <busby@pnts.com>
  2003-07-01 09:59       ` Re: Best way to convert PG's TIMESTAMPTZ to PHP DATE? Michael Hanna <zen@hwcn.org>
  1 sibling, 1 reply; 9+ messages in thread

From: David Busby @ 2003-07-01 01:42 UTC (permalink / raw)
  To: Michael Hanna <zen@hwcn.org>; +Cc: pgsql-php

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




^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Best way to convert PG's TIMESTAMPTZ to PHP DATE?
  2003-06-30 19:54 Best way to convert PG's TIMESTAMPTZ to PHP DATE? Michael Hanna <zen@hwcn.org>
  2003-06-30 20:06 ` Re: Best way to convert PG's TIMESTAMPTZ to PHP DATE? David Busby <busby@pnts.com>
  2003-07-01 01:19   ` Re: Best way to convert PG's TIMESTAMPTZ to PHP DATE? Michael Hanna <zen@hwcn.org>
  2003-07-01 01:42     ` Re: Best way to convert PG's TIMESTAMPTZ to PHP DATE? David Busby <busby@pnts.com>
@ 2003-07-01 09:59       ` Michael Hanna <zen@hwcn.org>
  0 siblings, 0 replies; 9+ messages in thread

From: Michael Hanna @ 2003-07-01 09:59 UTC (permalink / raw)
  To: David Busby <busby@pnts.com>; +Cc: pgsql-php

Not sure why this results in nothing:

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

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

Finally, if I ever get the output, would nested for-loops properly 
output the information such that for each date printed, it will print 
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 
> 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));
>>
>
>
> ---------------------------(end of 
> broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faqs/FAQ.html
>




^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Best way to convert PG's TIMESTAMPTZ to PHP DATE?
  2003-06-30 19:54 Best way to convert PG's TIMESTAMPTZ to PHP DATE? Michael Hanna <zen@hwcn.org>
  2003-06-30 20:06 ` Re: Best way to convert PG's TIMESTAMPTZ to PHP DATE? David Busby <busby@pnts.com>
  2003-07-01 01:19   ` Re: Best way to convert PG's TIMESTAMPTZ to PHP DATE? Michael Hanna <zen@hwcn.org>
@ 2003-07-01 01:43     ` David Busby <busby@pnts.com>
  1 sibling, 0 replies; 9+ messages in thread

From: David Busby @ 2003-07-01 01:43 UTC (permalink / raw)
  To: Michael Hanna <zen@hwcn.org>; +Cc: pgsql-php

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




^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Best way to convert PG's TIMESTAMPTZ to PHP DATE?
  2003-06-30 19:54 Best way to convert PG's TIMESTAMPTZ to PHP DATE? Michael Hanna <zen@hwcn.org>
@ 2003-06-30 21:27 ` Jeff <jam@zoidtechnologies.com>
  2003-06-30 21:35   ` Re: Best way to convert PG's TIMESTAMPTZ to PHP DATE? Jeff <jam@zoidtechnologies.com>
  2 siblings, 1 reply; 9+ messages in thread

From: Jeff @ 2003-06-30 21:27 UTC (permalink / raw)
  To: Michael Hanna <zen@hwcn.org>; +Cc: pgsql-php@postgresql.org, Jeff <jam@zoidtechnologies.com>

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



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Best way to convert PG's TIMESTAMPTZ to PHP DATE?
  2003-06-30 19:54 Best way to convert PG's TIMESTAMPTZ to PHP DATE? Michael Hanna <zen@hwcn.org>
  2003-06-30 21:27 ` Re: Best way to convert PG's TIMESTAMPTZ to PHP DATE? Jeff <jam@zoidtechnologies.com>
@ 2003-06-30 21:35   ` Jeff <jam@zoidtechnologies.com>
  0 siblings, 0 replies; 9+ messages in thread

From: Jeff @ 2003-06-30 21:35 UTC (permalink / raw)
  To: Michael Hanna <zen@hwcn.org>; +Cc: pgsql-php@postgresql.org, Jeff <jam@zoidtechnologies.com>

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



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Best way to convert PG's TIMESTAMPTZ to PHP DATE?
  2003-06-30 19:54 Best way to convert PG's TIMESTAMPTZ to PHP DATE? Michael Hanna <zen@hwcn.org>
@ 2003-07-01 02:02 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  2 siblings, 0 replies; 9+ messages in thread

From: Christopher Kings-Lynne @ 2003-07-01 02:02 UTC (permalink / raw)
  To: Michael Hanna <zen@hwcn.org>; pgsql-php

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
> 



^ permalink  raw  reply  [nested|flat] 9+ messages in thread


end of thread, other threads:[~2003-07-01 09:59 UTC | newest]

Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-06-30 19:54 Best way to convert PG's TIMESTAMPTZ to PHP DATE? Michael Hanna <zen@hwcn.org>
2003-06-30 20:06 ` David Busby <busby@pnts.com>
2003-07-01 01:19   ` Michael Hanna <zen@hwcn.org>
2003-07-01 01:42     ` David Busby <busby@pnts.com>
2003-07-01 09:59       ` Michael Hanna <zen@hwcn.org>
2003-07-01 01:43     ` David Busby <busby@pnts.com>
2003-06-30 21:27 ` Jeff <jam@zoidtechnologies.com>
2003-06-30 21:35   ` Jeff <jam@zoidtechnologies.com>
2003-07-01 02:02 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>

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