By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,178 Members | 971 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,178 IT Pros & Developers. It's quick & easy.

Mailing

P: n/a
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--
Nov 23 '05 #1
Share this Question
Share on Google+
10 Replies


P: n/a
* Todd P Marek (af****@somahq.com) wrote:
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?


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

Nov 23 '05 #2

P: n/a
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:
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--

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

Nov 23 '05 #3

P: n/a
Todd P Marek <af****@somahq.com> writes:
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 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

Nov 23 '05 #4

P: n/a

On Tue, 5 Oct 2004, Todd P Marek wrote:
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


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

Nov 23 '05 #5

P: n/a
On Tue, 2004-10-05 at 09:32 -0500, Todd P Marek wrote:


__________________________________________________ ____________________

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


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 | ro***@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-----

Nov 23 '05 #6

P: n/a
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

Nov 23 '05 #7

P: n/a

On Oct 5, 2004, at 10:00 AM, Kevin Barnard wrote:
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.


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 ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #8

P: n/a
Hey, I didn't know "trim" is so flexible... cool !

Cheers,
Csaba.

On Tue, 2004-10-05 at 17:00, Kevin Barnard wrote:
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

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

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

Nov 23 '05 #9

P: n/a
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 <af****@somahq.com> wrote:

On Oct 5, 2004, at 10:00 AM, Kevin Barnard wrote:
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.


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 4: Don't 'kill -9' the postmaster

Nov 23 '05 #10

P: n/a
On Tue, Oct 05, 2004 at 10:00:29 -0500,
Kevin Barnard <ke***********@gmail.com> wrote:
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.


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

Nov 23 '05 #11

This discussion thread is closed

Replies have been disabled for this discussion.