Thread

1. Datetime in humane format

   Richard Lynch <lynch@lscorp.com> — 1998-09-06T04:27:08Z

   So, I followed the advice in the archives and used the datetime type.
   
   Now, I've got entries like 'Sun Sep 06 00:06:57 1998 EDT' in my table.
   
   So, how do I turn that into something that a normal human can read?
   
   "Sep 06 1998 12:06 am" would be nice...
   
   Or, how do I turn that back into seconds since epoch?  I'm using PHP which
   has a nice date function that I think I can coerce into giving me what I
   want... unless it also thinks that humans understand 00:06:57 as just after
   midnight...
   
   --
   --
   -- "TANSTAAFL" Rich lynch@lscorp.com
   
   
   
   

2. Re: [SQL] Datetime in humane format

   Herouth Maoz <herouth@oumail.openu.ac.il> — 1998-09-06T11:16:32Z

   At 6:27 +0200 on 6/9/98, Richard Lynch wrote:
   
   
   > So, I followed the advice in the archives and used the datetime type.
   >
   > Now, I've got entries like 'Sun Sep 06 00:06:57 1998 EDT' in my table.
   >
   > So, how do I turn that into something that a normal human can read?
   >
   > "Sep 06 1998 12:06 am" would be nice...
   >
   > Or, how do I turn that back into seconds since epoch?  I'm using PHP which
   > has a nice date function that I think I can coerce into giving me what I
   > want... unless it also thinks that humans understand 00:06:57 as just after
   > midnight...
   
   That depends on the country... To me, 24-hour clocks are as natural as
   hailing a cab.
   
   Seconds since epoch - very easy: the function date_part will do that for
   you. Read the manpage of pgbuiltin.
   
   Perhaps its worthwhile to delve into the PHP documentation. Perhaps you can
   transfer the result you got from a query on a date field directly to some
   PHP date type which allows formatting. At least in Java that's how it's
   done - so why not in others.
   
   Herouth
   
   --
   Herouth Maoz, Internet developer.
   Open University of Israel - Telem project
   http://telem.openu.ac.il/~herutma
   
   
   
   

3. Re: [SQL] Datetime in humane format

   Eric J McKeown <ericm@palaver.net> — 1998-09-08T00:32:21Z

   On Sat, 5 Sep 1998, Richard Lynch wrote:
   
   > Date: Sat, 5 Sep 1998 23:27:08 -0500
   > From: Richard Lynch <lynch@lscorp.com>
   > To: pgsql-sql@postgreSQL.org
   > Subject: [SQL] Datetime in humane format
   > 
   > So, I followed the advice in the archives and used the datetime type.
   > 
   > Now, I've got entries like 'Sun Sep 06 00:06:57 1998 EDT' in my table.
   > 
   > So, how do I turn that into something that a normal human can read?
   > 
   > "Sep 06 1998 12:06 am" would be nice...
   
   I've written a function to do just that using the datetime type and PHP.
   Here it is:
   
   function article_date_format($date) {
           $main = explode(" ", $date);
           $mdy = explode("/", $main[0]);
           $hms = explode(":", $main[1]);
           $sec = explode("\.", $hms[2]);
           $timestamp = mktime($hms[0], $hms[1], $sec[0], $mdy[0], $mdy[1],
   $mdy[2]);
           $new_date = date("l, F d, Y", $timestamp);
           $more_date = date("h:i A", $timestamp);
           if (substr($more_date, 0, 1) == "0"):
                   $more_date = substr($more_date, 1, strlen($more_date));
           endif;
           $new_date .= "&nbsp   $more_date";
           return $new_date;
   }                                                        
   
   --------------
   
   Before I execute that function, I send this command to Postgres (via PHP):
   
   SET DateStyle to 'SQL'
   
   to ensure that my function works properly on the dates I'm fetching.
   
   I'm not sure my solution is the most efficient one, but I know it works
   for me.  Hope it helps...
   
   eric
   
   > 
   > Or, how do I turn that back into seconds since epoch?  I'm using PHP which
   > has a nice date function that I think I can coerce into giving me what I
   > want... unless it also thinks that humans understand 00:06:57 as just after
   > midnight...
   > 
   > --
   > --
   > -- "TANSTAAFL" Rich lynch@lscorp.com
   > 
   > 
   > 
   > 
   
   _______________________
   Eric McKeown
   ericm@palaver.net
   http://www.palaver.net