pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Datetime in humane format
3+ messages / 3 participants
[nested] [flat]

* Datetime in humane format
@ 1998-09-06 04:27 Richard Lynch <lynch@lscorp.com>
  1998-09-06 11:16 ` Re: [SQL] Datetime in humane format Herouth Maoz <herouth@oumail.openu.ac.il>
  1998-09-08 00:32 ` Re: [SQL] Datetime in humane format Eric McKeown <ericm@palaver.net>
  0 siblings, 2 replies; 3+ messages in thread

From: lynch@lscorp.com @ 1998-09-06 04:27 UTC (permalink / raw)
  To: pgsql-sql

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





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

* Re: [SQL] Datetime in humane format
  1998-09-06 04:27 Datetime in humane format Richard Lynch <lynch@lscorp.com>
@ 1998-09-06 11:16 ` Herouth Maoz <herouth@oumail.openu.ac.il>
  1 sibling, 0 replies; 3+ messages in thread

From: Herouth Maoz @ 1998-09-06 11:16 UTC (permalink / raw)
  To: lynch@lscorp.com; pgsql-sql

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





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

* Re: [SQL] Datetime in humane format
  1998-09-06 04:27 Datetime in humane format Richard Lynch <lynch@lscorp.com>
@ 1998-09-08 00:32 ` Eric McKeown <ericm@palaver.net>
  1 sibling, 0 replies; 3+ messages in thread

From: Eric McKeown @ 1998-09-08 00:32 UTC (permalink / raw)
  To: Richard Lynch <lynch@lscorp.com>; +Cc: pgsql-sql

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




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


end of thread, other threads:[~1998-09-08 00:32 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1998-09-06 04:27 Datetime in humane format Richard Lynch <lynch@lscorp.com>
1998-09-06 11:16 ` Herouth Maoz <herouth@oumail.openu.ac.il>
1998-09-08 00:32 ` Eric McKeown <ericm@palaver.net>

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