Thread: Date Formatting
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
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
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
>
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
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> -----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
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
Is there a builtin function in Postgres to display the time as XX:XX am or XX:XX pm? Cheers, Tom Hodges
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
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
>
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?
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? >
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?
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