Date: Sun, 9 Oct 2005 21:13:12 -0600
From: Michael Fuhr <mike@fuhr.org>
To: Simon Law <simonslaw@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: convert time
Message-ID: <20051010031312.GA67346@winnie.fuhr.org>
References: <A0279243-5495-41E3-A5AD-46762CF75AC9@gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <A0279243-5495-41E3-A5AD-46762CF75AC9@gmail.com>

On Sun, Oct 09, 2005 at 07:20:53PM -0400, Simon Law wrote:
> Is there a way for postgres to output  using the 12 hour standard  
> instead of army time? The column is of type time

By "army time" I assume you mean 24-hour time, which is widely used
in non-military settings, especially outside the United States.  In
many countries, 24-hour time is just as "standard" as 12-hour time,
and is understood by the population at large because it's the usual
format for TV and radio schedules, train and bus timetables, etc.

  It was a bright cold day in April, and the clocks were striking
  thirteen.
  --Opening sentence of _1984_ by George Orwell

You can use to_char() to format time types, although it apparently
works via an implicit cast to interval, as Bruce Momjian explains
in the following message:

http://archives.postgresql.org/pgsql-bugs/2005-02/msg00245.php

See "Data Type Formatting Functions" in the documentation to learn
how to use to_char():

http://www.postgresql.org/docs/8.0/interactive/functions-formatting.html

Example:

CREATE TABLE foo (t time);

INSERT INTO foo (t) VALUES ('08:00:00');
INSERT INTO foo (t) VALUES ('20:00:00');

SELECT t, to_char(t, 'HH12:MI:SSam') FROM foo;
    t     |  to_char   
----------+------------
 08:00:00 | 08:00:00am
 20:00:00 | 08:00:00pm
(2 rows)

-- 
Michael Fuhr