Thread: Mailing
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
<fontfamily><param>Verdana</param><smaller>06:30:00 AM - 07:30:00 AM</smaller></fontfamily>
I am really looking to get it outputting like this.
<fontfamily><param>Verdana</param><smaller> 6:30 AM - 7:30 AM
</smaller></fontfamily>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<fontfamily><param>Verdana</param><smaller>
</smaller></fontfamily><italic>"If you think you understand something
it's habit."
--Gary Kraftsow--</italic>
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:
> 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
Attachment
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--
Todd P Marek <affe23@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
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';
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/current/static/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 ****************************************/
Attachment
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.
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
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
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: > > > > 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 > >
On Tue, Oct 05, 2004 at 10:00:29 -0500, Kevin Barnard <kevin.barnard@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?