[freeside] date format

[David B. Hughes]

I found the SQL query, however I'm still looking for
the solution to convert this via perl Date::Format.
Looks like I may need to break down and purchase some
books on Perl and Postgres.

"Example for the cust_credit_date column"
SELECT date(cust_credit._date)
FROM cust_credit
; resulting format > yyyy/mm/dd

or

SELECT datetime(cust_credit._date)
FROM cust_credit
; resulting format > yyyy/mm/dd hh:mm:ss

Some possible workarounds:
1. Set DateStyle To 'value'
2. A new Function with a "to_char"

Dave

-----Original Message-----
From: ivan at 420.am [mailto:ivan at 420.am]
Sent: Friday, August 16, 2002 1:50 PM
To: ivan-freeside at sisd.com
Subject: Re: [freeside] date format
styletests=IN_REP_TO,NO_REAL_NAME,DOUBLE_CAPSWORDversion=2.31


On Fri, Aug 16, 2002 at 02:00:39AM -0700, baloo at gimpgirl.com wrote:
> On Fri, 16 Aug 2002 ivan at 420.am wrote:
>
> > On Fri, Aug 16, 2002 at 01:29:31AM -0700, baloo at gimpgirl.com wrote:
> > > On Thu, 15 Aug 2002, David B. Hughes wrote:
> > >
> (snipped misread/half-read question)
> > >
> > > select web_page from the_postgres_online_doc_web_site where url =
> > >
'http://www.us.postgresql.org/users-lounge/docs/7.2/postgres/sql-set.html';
> >
> > Hmm, I can't see that includes any documentation on how to convert from
> > UNIX integer timestamps to a formatted date.
>
> *sigh* "Self?" "Hmm?" "Don't *LART* answer questions *LART* before coffee.
> *LART* *LART* *LART*"

Hey, it isn't like I don't do the same on occasion.  :)  Dont' sweat it.

> > I'd be interested hearing about an SQL equivalent.  I don't know of one
> > offhand.
>
> Postgresql has a timestamp type. It uses a different origin than Unix
> timestamps (January 1, 2000 at some unspecified, but figureable, time and
> timezone), but converting to them, and then to a date type, should be
> possible.

I spent a while with the PostgreSQL manual and in psql, and I couldn't
come up with anything.  As I said, I'd be interested in hearing about an
SQL equivalent to Perl's Date::Format that would convert UNIX timestamps
to human-readable dates.

--
_ivan