Hello-

I am in the process of translating a site using mysql as the backend
over to postgres. I have a lot of time data that I would like to
display to the user in the form of a schedule.

I am using the to_char function to make the times human friendly

to_char(class_schedule.endtime, 'HH:MI:SS AM')

which returns

06:30:00 AM - 07:30:00 AM

I am really looking to get it outputting like this.

6:30 AM - 7:30 AM

I have looked through the documentation and haven't found anything to
do this in postgres. I am going to have to do this formating in the
application layer?

Thanks
Todd Marek

"If you think you understand something it's habit."
--Gary Kraftsow--

* Todd P Marek (affe23@somahq.com) wrote:[color=blue]
> I have looked through the documentation and haven't found anything to
> do this in postgres. I am going to have to do this formating in the
> application layer?[/color]

If nothing else I'd think you could create your own function in Postgres
to display the time however you like it.. Someone may have a better
suggestion, but I wouldn't expect that to be very difficult.

Stephen

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (GNU/Linux)

iD8DBQFBYrMlrzgMPqB3kigRAp4KAJ9XlP5rkLOk6d4zm5U5R+ fXJxRw3ACeLoEH
7lyDhCDLf+e+AS8F95PLYYo=
=qdIW
-----END PGP SIGNATURE-----

I would thought it would be an obvious try:

cnagy=> select to_char(now(), 'HH:MM AM');
to_char
----------
04:10 PM
(1 row)

HTH,
Csaba.


On Tue, 2004-10-05 at 16:32, Todd P Marek wrote:[color=blue]
> Hello-
>
> I am in the process of translating a site using mysql as the
> backendover to postgres. I have a lot of time data that I would like
> todisplay to the user in the form of a schedule.
>
> I am using the to_char function to make the times human friendly
>
> to_char(class_schedule.endtime, 'HH:MI:SS AM')
>
> which returns
>
> 06:30:00 AM - 07:30:00 AM
>
> I am really looking to get it outputting like this.
>
> 6:30 AM - 7:30 AM
>
> I have looked through the documentation andhaven't found anything to
> do this in postgres. I am going to have todo this formating in the
> application layer?
>
> Thanks
> Todd Marek
>
> "If you think you understand somethingit's habit."
> --Gary Kraftsow--[/color]


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Todd P Marek <affe23@somahq.com> writes:[color=blue]
> I am using the to_char function to make the times human friendly
> to_char(class_schedule.endtime, 'HH:MI:SS AM')
> which returns
> 06:30:00 AM - 07:30:00 AM[/color]
[color=blue]
> I am really looking to get it outputting like this.
> 6:30 AM - 7:30 AM[/color]
[color=blue]
> I have looked through the documentation and haven't found anything to
> do this in postgres.[/color]

I think you want 'FMHH:MI AM' ... if not, you'd better be more specific
about what you want.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

On Tue, 5 Oct 2004, Todd P Marek wrote:
[color=blue]
> Hello-
>
> I am in the process of translating a site using mysql as the backend
> over to postgres. I have a lot of time data that I would like to
> display to the user in the form of a schedule.
>
> I am using the to_char function to make the times human friendly
>
> to_char(class_schedule.endtime, 'HH:MI:SS AM')
>
> which returns
>
> 06:30:00 AM - 07:30:00 AM
>
> I am really looking to get it outputting like this.
>
> 6:30 AM - 7:30 AM[/color]

For the seconds, do you want seconds if it's not 00, or do you just not
want seconds at all? Because removing :SS will get rid of the seconds
display.

For the leading 0s, you'd probably need to do a user defined function to
trim them off, but it'd probably be relatively simple use of ltrim, so you
might do something like:

create function format_time(time) returns text as '
select ltrim(to_char($1, ''HH:MI AM''), ''0'')' language 'sql';

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

On Tue, 2004-10-05 at 09:32 -0500, Todd P Marek wrote:[color=blue]
>
>
> __________________________________________________ ____________________
>
> Hello-
>
> I am in the process of translating a site using mysql as the backend
> over to postgres. I have a lot of time data that I would like to
> display to the user in the form of a schedule.
>
> I am using the to_char function to make the times human friendly
>
> to_char(class_schedule.endtime, 'HH:MI:SS AM')
>
> which returns
>
> 06:30:00 AM - 07:30:00 AM
>
> I am really looking to get it outputting like this.
>
> 6:30 AM - 7:30 AM
> [/color]

Yeah, you're getting exactly what you're asking PostgreSQL to give you.
Drop the ':SS' if you don't want the seconds.

You can find out more about how you can format your date/times here:
http://www.postgresql.org/docs/curre...tic/functions-
formatting.html#FUNCTIONS-FORMATTING-DATETIME-TABLE


Good luck,

Robby


--
/***************************************
* Robby Russell | Owner.Developer.Geek
* PLANET ARGON | www.planetargon.com
* Portland, OR | robby@planetargon.com
* 503.351.4730 | blog.planetargon.com
* PHP/PostgreSQL Hosting & Development
****************************************/


-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.6 (GNU/Linux)

iD8DBQBBYraO0QaQZBaqXgwRAkNMAJ0dYCowvc0k42puC2tP+K YUdZ0hSwCeLVRQ
EPqNgF0zbXg6V4qTmPjJLwQ=
=TM6S
-----END PGP SIGNATURE-----

SELECT trim(leading '0' from to_char(now(), 'HH:MM AM'))

I think is what you really want. This gets rid of the nasty leasing 0.

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

On Oct 5, 2004, at 10:00 AM, Kevin Barnard wrote:
[color=blue]
> SELECT trim(leading '0' from to_char(now(), 'HH:MM AM'))
>
> I think is what you really want. This gets rid of the nasty leasing 0.[/color]

I wasn't even paying attention to the seconds. I was in fact talking
about the leading 0.

Thanks to everyone and apologies for my oversight of the seconds clause.

Todd Marek


---------------------------(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

Hey, I didn't know "trim" is so flexible... cool !

Cheers,
Csaba.

On Tue, 2004-10-05 at 17:00, Kevin Barnard wrote:[color=blue]
> SELECT trim(leading '0' from to_char(now(), 'HH:MM AM'))
>
> I think is what you really want. This gets rid of the nasty leasing 0.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html[/color]


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Going back to the documents I think Tom's answer of prepending FM is
better then mine. Look at table 9-22 for other options

On Tue, 5 Oct 2004 10:06:51 -0500, Todd P Marek <affe23@somahq.com> wrote:[color=blue]
>
>
>
> On Oct 5, 2004, at 10:00 AM, Kevin Barnard wrote:
>[color=green]
> > SELECT trim(leading '0' from to_char(now(), 'HH:MM AM'))
> >
> > I think is what you really want. This gets rid of the nasty leasing 0.[/color]
>
> I wasn't even paying attention to the seconds. I was in fact talking
> about the leading 0.
>
> Thanks to everyone and apologies for my oversight of the seconds clause.
>
> Todd Marek
>
>[/color]

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

On Tue, Oct 05, 2004 at 10:00:29 -0500,
Kevin Barnard <kevin.barnard@gmail.com> wrote:[color=blue]
> SELECT trim(leading '0' from to_char(now(), 'HH:MM AM'))
>
> I think is what you really want. This gets rid of the nasty leasing 0.[/color]

Won't that be a problem for times between 0000 and 0059?

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org