pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Mark Kirkwood <markir@ihug.co.nz>
To: forgacs@negral.com
To: pgsql-sql@postgreSQL.org
To: efte@negral.com
Subject: RE: DATE Formating Problem Again
Date: Thu, 02 Mar 2000 22:54:54 +1300
Message-ID: <38BE3A6E.D18B24C9@ihug.co.nz> (raw)
References: <38B77942.8B5B6A85@ihug.co.nz>
	<26472.951552291@sss.pgh.pa.us>
	<38B8364F.FD8D1D3D@ihug.co.nz>
	<15360.951606889@sss.pgh.pa.us>
	<38B9B9E6.82914F15@ihug.co.nz>

>I am searching after the date formatting solution, but didn't found a
>correct one.
>
>So example ORACLE has
>    - to_char function to make any kind of date format from a date
type:
>to_char(now,'yyyy.mm.dd')
>    - to_date to makes a date type from any kind of string: to_date
>('1999.11.23','yyyy.mm.dd')
>(sniped...)

I have wrestled a bit with this myself - being an Oracle dba ( but i'm
alright now....),
the date <-> char conversion is very different is Postgresql.

However I have found that the "date_part" and "interval" functions seem
to do the job ok,
sometimes combined with the casting operator  "::datetime".

e.g : ( for release 7.0 )
date -> number

db1=> select date_part('year',now());
 date_part
-----------
      2000

and  char -> interval
db1=> select interval('4 hours'::interval);
 ?column?
----------
 04:00

and char -> datetime via interval
db1=> select ('1999-01-01 '||interval('4 hours'::interval))::datetime;
        ?column?
------------------------
 1999-01-01 04:00:00+13

I hope this helps a bit...

Cheers

Mark




view thread (2+ messages)  latest in thread

Message-ID: <38BE3A6E.D18B24C9@ihug.co.nz>
Permalink:  ../38BE3A6E.D18B24C9@ihug.co.nz/
Also on:    postgresql.org/message-id/38BE3A6E.D18B24C9@ihug.co.nz

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: markir@ihug.co.nz, forgacs@negral.com, efte@negral.com
  Subject: Re: DATE Formating Problem Again
  In-Reply-To: <38BE3A6E.D18B24C9@ihug.co.nz>
  References: <38B77942.8B5B6A85@ihug.co.nz> <26472.951552291@sss.pgh.pa.us> <38B8364F.FD8D1D3D@ihug.co.nz> <15360.951606889@sss.pgh.pa.us> <38B9B9E6.82914F15@ihug.co.nz> <38BE3A6E.D18B24C9@ihug.co.nz>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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