Thread: Date Formatting

Date Formatting

From
Tom Ansley
Date:
I am writing timestamps to file and the formatting is currently 2002-06-06
00:00:00.0.  Is there a function to only write the date and also to format it
something like MM/dd/yyyy   ?

Thanks

Tom

Re: Date Formatting

From
Oliver Elphick
Date:
On Fri, 2002-05-03 at 20:31, Tom Ansley wrote:
> I am writing timestamps to file and the formatting is currently 2002-06-06
> 00:00:00.0.  Is there a function to only write the date and also to format it
> something like MM/dd/yyyy   ?

to_char(timestamp,format_text)

For your particular example, format_text = 'MM/DD/YYYY'

Users' Manual section 4.7

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C

     "Live in harmony with one another. Do not be proud, but
      be willing to associate with people of low position.
      Do not be conceited."            Romans 12:16

Attachment

Re: Date Formatting

From
hodges@xprt.net
Date:
This may give you what you need:

 date("M d Y H:i:s",$timestamp)


On another note, is there a builtin command to change
time display from 19:00 to 7:00 pm?

Tom Hodges

On 3 May 2002 at 13:31, Tom Ansley wrote:

> I am writing timestamps to file and the formatting is currently 2002-06-06
> 00:00:00.0.  Is there a function to only write the date and also to format it
> something like MM/dd/yyyy   ?
>
> Thanks
>
> Tom
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>



Re: Date Formatting

From
Doug Silver
Date:
Hi Tom -

select cast (now() as date) as date;
    date
------------
 2002-05-03

Depending on your application (e.g. perl/php script), that output is easy
enough change it around to what you want or you could certainly write a
function to do it -- something I have yet to do!  I think such a function
would be fairly easy to do since you're just rearranging the
output, but doesn't everything look easy at the outset?  You didn't
mention the context of what you need this for, so there are lots of ways
to do this.

-doug

On Fri, 3 May 2002, Tom Ansley wrote:

> I am writing timestamps to file and the formatting is currently 2002-06-06
> 00:00:00.0.  Is there a function to only write the date and also to format it
> something like MM/dd/yyyy   ?
>
> Thanks
>
> Tom
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>

--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Doug Silver
Network Manager
Urchin Corporation    http://www.urchin.com
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


Re: Date Formatting

From
"Joel Burton"
Date:
> -----Original Message-----
> From: pgsql-novice-owner@postgresql.org
> [mailto:pgsql-novice-owner@postgresql.org]On Behalf Of Doug Silver
> Sent: Friday, May 03, 2002 5:06 PM
> To: Tom Ansley
> Cc: pgsql-novice@postgresql.org
> Subject: Re: [NOVICE] Date Formatting
>
>
> Hi Tom -
>
> select cast (now() as date) as date;
>     date
> ------------
>  2002-05-03
>
> Depending on your application (e.g. perl/php script), that output is easy
> enough change it around to what you want or you could certainly write a
> function to do it -- something I have yet to do!  I think such a function
> would be fairly easy to do since you're just rearranging the
> output, but doesn't everything look easy at the outset?  You didn't
> mention the context of what you need this for, so there are lots of ways
> to do this.
>
> -doug
>
> On Fri, 3 May 2002, Tom Ansley wrote:
>
> > I am writing timestamps to file and the formatting is currently
> 2002-06-06
> > 00:00:00.0.  Is there a function to only write the date and
> also to format it
> > something like MM/dd/yyyy   ?
> >
> > Thanks
> >
> > Tom

More generically, look at the docs for to_char(), which is very flexible
formatting for date fields (& other types, too)

- J.

Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton
Knowledge Management & Technology Consultant


Re: Date Formatting

From
Richard A Lough
Date:
Doug Silver wrote:
>
> Hi Tom -
>
> select cast (now() as date) as date;
>     date
> ------------
>  2002-05-03
>
> Depending on your application (e.g. perl/php script), that output is easy
> enough change it around to what you want or you could certainly write a
> function to do it -- something I have yet to do!  I think such a function
> would be fairly easy to do since you're just rearranging the
> output, but doesn't everything look easy at the outset?  You didn't
> mention the context of what you need this for, so there are lots of ways
> to do this.
>
> -doug
>
> On Fri, 3 May 2002, Tom Ansley wrote:
>
> > I am writing timestamps to file and the formatting is currently 2002-06-06
> > 00:00:00.0.  Is there a function to only write the date and also to format it
> > something like MM/dd/yyyy   ?
> >

SET DATESTYLE 'Postgres, US';

might get you part of the way there. You might need to manipulate
the output using sed/awk/Perl to get exactly what you want

To get back to the default use 'ISO' or 'European' depending on
where you started from.

HTH

Richard A Lough

Time Formatting

From
hodges@xprt.net
Date:
Is there a builtin function in Postgres to display the
time as XX:XX am or XX:XX pm?

Cheers, Tom Hodges

Re: Time Formatting

From
Larry Rosenman
Date:
On Thu, 2002-05-09 at 20:37, hodges@xprt.net wrote:
> Is there a builtin function in Postgres to display the
> time as XX:XX am or XX:XX pm?
>
look at the to_char function on
http://www.lerctr.org/postgresql/functions-formatting.html

(this is my copy of the docs).


> Cheers, Tom Hodges
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
--
Larry Rosenman                     http://www.lerctr.org/~ler
Phone: +1 972-414-9812                 E-Mail: ler@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749


Re: Time Formatting

From
hodges@xprt.net
Date:
I have 3 queries and some php to run them.  What I need is
to display the time in a HH:MM am format.  Doesn't need to
be stored that way in the table?


These two add or update records:

insert into feasts (timestamp, date, time, address, direction,
host, phone)
values ('current_timestamp', '$date', '$time','$addr',
'$directions','$host','$phone');

update feasts set date='$date', time='$time', host='$host',
phone='$phon', address=$addr','direction='$directions'
where feast_id='$feast_id';


These retrieve and display records:

select * from feasts;

$time = $result->fields[3];

and in a form,
print ("<INPUT TYPE= \"text\" NAME=\"time\" VALUE= \"$time\"><BR>\n");
and
print ("<INPUT TYPE= \"text\" NAME=\"time\">\n");

So I am not sure how to do this.

Thanks,
Tom

On 9 May 2002 at 20:55, Larry Rosenman wrote:

> On Thu, 2002-05-09 at 20:37, hodges@xprt.net wrote:
> > Is there a builtin function in Postgres to display the
> > time as XX:XX am or XX:XX pm?
> >
> look at the to_char function on
> http://www.lerctr.org/postgresql/functions-formatting.html
>
> (this is my copy of the docs).
>
>
> > Cheers, Tom Hodges
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo@postgresql.org so that your
> > message can get through to the mailing list cleanly
> >
> --
> Larry Rosenman                     http://www.lerctr.org/~ler
> Phone: +1 972-414-9812                 E-Mail: ler@lerctr.org
> US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>



Re: Time Formatting

From
Andrew McMillan
Date:
On Fri, 2002-05-10 at 15:30, hodges@xprt.net wrote:
> I have 3 queries and some php to run them.  What I need is
> to display the time in a HH:MM am format.  Doesn't need to
> be stored that way in the table?

Right, so instead of:

SELECT timestamp_field FROM mytable;

you want:

SELECT to_char( timestamp_field, 'HH12:MI am') FROM mytable;

And for lots more information, look at the docs:

>
> On 9 May 2002 at 20:55, Larry Rosenman wrote:
>
> > look at the to_char function on
> > http://www.lerctr.org/postgresql/functions-formatting.html
> >

Regards,
                Andrew.
--
--------------------------------------------------------------------
Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/        PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201    MOB: +64(21)635-694    OFFICE: +64(4)499-2267
       Are you enrolled at http://schoolreunions.co.nz/ yet?


Re: Time Formatting

From
hodges@xprt.net
Date:
SELECT to_char( timestamp, 'HH12:MI am') FROM feasts;
or                time
from psql prompt gives
Function 'to_char(time, unknown)' does not exist
Unable to identify a function that satisfies the given argument types
you may need to add explicit typecasts
time has the type of time

Any idea?

Tom

> On Fri, 2002-05-10 at 15:30, hodges@xprt.net wrote:
> > I have 3 queries and some php to run them.  What I need is
> > to display the time in a HH:MM am format.  Doesn't need to
> > be stored that way in the table?
>
> Right, so instead of:
>
> SELECT timestamp_field FROM mytable;
>
> you want:
>
> SELECT to_char( timestamp_field, 'HH12:MI am') FROM mytable;
>
> And for lots more information, look at the docs:
>
> >
> > On 9 May 2002 at 20:55, Larry Rosenman wrote:
> >
> > > look at the to_char function on
> > > http://www.lerctr.org/postgresql/functions-formatting.html
> > >
>
> Regards,
>     Andrew.
> --
> --------------------------------------------------------------------
> Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington
> WEB: http://catalyst.net.nz/        PHYS: Level 2, 150-154 Willis St
> DDI: +64(4)916-7201    MOB: +64(21)635-694    OFFICE: +64(4)499-2267
>        Are you enrolled at http://schoolreunions.co.nz/ yet?
>



Re: Time Formatting

From
Andrew McMillan
Date:
On Sat, 2002-05-11 at 13:31, hodges@xprt.net wrote:
> SELECT to_char( timestamp, 'HH12:MI am') FROM feasts;
> or                time
> from psql prompt gives
> Function 'to_char(time, unknown)' does not exist
> Unable to identify a function that satisfies the given argument types
> you may need to add explicit typecasts
> time has the type of time

SELECT to_char( mytimefield::timestamp, 'HH12:MI am') FROM feasts;

And I can't recommend strongly enough that you read the manual, which is
quite clear on this sort of thing.

Regards,
                    Andrew.

> >
> > And for lots more information, look at the docs:
> >
> > >
> > > On 9 May 2002 at 20:55, Larry Rosenman wrote:
> > >
> > > > look at the to_char function on
> > > > http://www.lerctr.org/postgresql/functions-formatting.html
> > > >

--
--------------------------------------------------------------------
Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/        PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201    MOB: +64(21)635-694    OFFICE: +64(4)499-2267
       Are you enrolled at http://schoolreunions.co.nz/ yet?


Re: Time Formatting

From
hodges@xprt.net
Date:
Problem solved.

I read the manual.  The to_char function doesn't work
with type time.  When I cast it to type timestamp the
database library I am using (php_lib_login and ADODB)
objects.

So I solved it with the php date and strtotime functions.
$time = $result->fields[3];
$time2 = (date("h:i a",strtotime($time)));

This allows flexible time display.  My update and add
buttons both store correct data in the table.

Thanks for all the helpful suggestions,

Tom


On Sat, 2002-05-11 at 13:31, hodges@xprt.net wrote:
> SELECT to_char( timestamp, 'HH12:MI am') FROM feasts;
> or                time
> from psql prompt gives
> Function 'to_char(time, unknown)' does not exist
> Unable to identify a function that satisfies the given argument types
> you may need to add explicit typecasts
> time has the type of time

SELECT to_char( mytimefield::timestamp, 'HH12:MI am') FROM feasts;

And I can't recommend strongly enough that you read the manual, which is
quite clear on this sort of thing.

Regards,
     Andrew.

> >
> > And for lots more information, look at the docs:
> >
> > >
> > > On 9 May 2002 at 20:55, Larry Rosenman wrote:
> > >
> > > > look at the to_char function on
> > > > http://www.lerctr.org/postgresql/functions-formatting.html
> > > >

--
--------------------------------------------------------------------
Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/        PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201    MOB: +64(21)635-694    OFFICE: +64(4)499-2267
       Are you enrolled at http://schoolreunions.co.nz/ yet?


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly