Обсуждение: Sorting on fieldtype Time?
I have a small problem.. I need to sort some rows on a Time field, but the time in these rows are recorded on evenings and nights, from 2200 to 0600 for instance. The problem is that the time after midnight should come after the time before midnight, i.e. 2315, 2345, 0015, 0100 should be sorted in this order and not 0015, 0100, 2315,2345. Is there a solution to this, other than using Timestamp instead of Time? Regards, BTJ ----------------------------------------------------------------------------------------------- Bjørn T Johansen (BSc,MNIF) Executive Manager btj@havleik.no Havleik Consulting Phone : +47 67 54 15 17 Conradisvei 4 Fax : +47 67 54 13 91 N-1338 Sandvika Cellular : +47 926 93 298 http://www.havleik.no ----------------------------------------------------------------------------------------------- "The stickers on the side of the box said "Supported Platforms: Windows 98, Windows NT 4.0, Windows 2000 or better", so clearly Linux was a supported platform." -----------------------------------------------------------------------------------------------
On Wed, Jul 09, 2003 at 11:24:14AM +0200, Bjorn T Johansen wrote: > Is there a solution to this, other than using Timestamp instead of Time? Not one I can think of offhand; but what's wrong with timestamp? A ---- Andrew Sullivan 204-4141 Yonge Street Liberty RMS Toronto, Ontario Canada <andrew@libertyrms.info> M2P 2A8 +1 416 646 3304 x110
> I have a small problem.. I need to sort some rows on a Time field, but > the time in these rows are recorded on evenings and nights, from 2200 to > 0600 for instance. The problem is that the time after midnight should > come after the time before midnight, i.e. 2315, 2345, 0015, 0100 should > be sorted in this order and not 0015, 0100, 2315,2345. > Is there a solution to this, other than using Timestamp instead of Time? You can try subtracting a fixed amount to transfer all times to before midnight and then sort. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
Nothing wrong but I don't use the Date part.... BTJ > On Wed, Jul 09, 2003 at 11:24:14AM +0200, Bjorn T Johansen wrote: >> Is there a solution to this, other than using Timestamp instead of Time? > > Not one I can think of offhand; but what's wrong with timestamp? > > A > > ---- > Andrew Sullivan 204-4141 Yonge Street > Liberty RMS Toronto, Ontario Canada > <andrew@libertyrms.info> M2P 2A8 > +1 416 646 3304 x110 > > > ---------------------------(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 Wed, Jul 09, 2003 at 11:24:14 +0200, Bjorn T Johansen <btj@havleik.no> wrote: > I have a small problem.. I need to sort some rows on a Time field, but > the time in these rows are recorded on evenings and nights, from 2200 to > 0600 for instance. The problem is that the time after midnight should > come after the time before midnight, i.e. 2315, 2345, 0015, 0100 should > be sorted in this order and not 0015, 0100, 2315,2345. > Is there a solution to this, other than using Timestamp instead of Time? You could do something like like: select timecol from timetab order by timecol <= 1200, timecol;
You are absolutely correct, I could... :) Thanks.... BTJ On Wed, 2003-07-09 at 15:48, Bruno Wolff III wrote: > On Wed, Jul 09, 2003 at 11:24:14 +0200, > Bjorn T Johansen <btj@havleik.no> wrote: > > I have a small problem.. I need to sort some rows on a Time field, but > > the time in these rows are recorded on evenings and nights, from 2200 to > > 0600 for instance. The problem is that the time after midnight should > > come after the time before midnight, i.e. 2315, 2345, 0015, 0100 should > > be sorted in this order and not 0015, 0100, 2315,2345. > > Is there a solution to this, other than using Timestamp instead of Time? > > You could do something like like: > > select timecol from timetab order by timecol <= 1200, timecol;