I need to sort some data based on a Time field and the times can cross
both midnight and noon. As far as I can tell, there is no way to solve
this without also supplying a date or am I missing something?
Regards,
BTJ 19 1506
On Monday 29 September 2003 10:11, Bjørn T Johansen wrote: I need to sort some data based on a Time field and the times can cross both midnight and noon. As far as I can tell, there is no way to solve this without also supplying a date or am I missing something?
You don't say when your "period" starts. This puts me in the same position as
PostgreSQL - I can't tell you whether 03:00 represents an early or a late
time in your period.
I presume you have a situation where a period starts at e.g. 06:00:00 and
continues until 05:59:59 the following day.
You could do something like:
SELECT
my_time,
CASE
WHEN my_time < '06:00:00' THEN my_time+'18 hours'::interval
ELSE my_time - '6 hours'::interval
END
AS sort_time
FROM
time_table
ORDER BY
sort_time
That would translate:
my_time sort_time
06:00:00 => 00:00:00
07:00:00 => 01:00:00
00:00:00 => 18:00:00
05:59:59 => 23:59:59
You could wrap that up in an SQL function if you wanted, or even add an index
on the function (but check the manual for details how).
--
Richard Huxton
Archonet Ltd
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
The problem is that I don't have such a "period". I can have a select
containing these data:
2350
0110
0330
which then should be sorted like that.
And I can also have the following:
1030
1145
1240
(also sorted as shown...)
the only thing I know for sure, is that the interval between the first
record and the last, is always less than 24 hours...
BTJ
On Mon, 2003-09-29 at 13:21, Richard Huxton wrote: On Monday 29 September 2003 10:11, Bjørn T Johansen wrote: I need to sort some data based on a Time field and the times can cross both midnight and noon. As far as I can tell, there is no way to solve this without also supplying a date or am I missing something?
You don't say when your "period" starts. This puts me in the same position as PostgreSQL - I can't tell you whether 03:00 represents an early or a late time in your period.
I presume you have a situation where a period starts at e.g. 06:00:00 and continues until 05:59:59 the following day.
You could do something like:
SELECT my_time, CASE WHEN my_time < '06:00:00' THEN my_time+'18 hours'::interval ELSE my_time - '6 hours'::interval END AS sort_time FROM time_table ORDER BY sort_time
That would translate: my_time sort_time 06:00:00 => 00:00:00 07:00:00 => 01:00:00 00:00:00 => 18:00:00 05:59:59 => 23:59:59
You could wrap that up in an SQL function if you wanted, or even add an index on the function (but check the manual for details how).
On Monday 29 September 2003 12:26, Bjørn T Johansen wrote: The problem is that I don't have such a "period". I can have a select containing these data:
2350 0110 0330
which then should be sorted like that.
And I can also have the following:
1030 1145 1240
(also sorted as shown...)
the only thing I know for sure, is that the interval between the first record and the last, is always less than 24 hours...
And how do you know that the first example shouldn't have been
0110
0330
2350
--
Richard Huxton
Archonet Ltd
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)
No, not really
perhaps you can do a ORDER BY (oid || yourtimefield). So you have the
RecordOrder in the way the records where inserted.
Daniel
I need to sort some data based on a Time field and the times can cross
both midnight and noon. As far as I can tell, there is no way to solve
this without also supplying a date or am I missing something?
Regards,
BTJ
Well, I don't.. But normal timespan is about 6-7 hours +- (so one can
assume max timespan = 12 hours really...)
BTJ
On Mon, 2003-09-29 at 13:40, Richard Huxton wrote: On Monday 29 September 2003 12:26, Bjørn T Johansen wrote: The problem is that I don't have such a "period". I can have a select containing these data:
2350 0110 0330
which then should be sorted like that.
And I can also have the following:
1030 1145 1240
(also sorted as shown...)
the only thing I know for sure, is that the interval between the first record and the last, is always less than 24 hours...
And how do you know that the first example shouldn't have been 0110 0330 2350
Yes, I could do that... I was just hoping to catch the odd times when
records aren't inserted in order....
BTJ
On Mon, 2003-09-29 at 13:44, Daniel Schuchardt wrote: No, not really
perhaps you can do a ORDER BY (oid || yourtimefield). So you have the RecordOrder in the way the records where inserted.
Daniel I need to sort some data based on a Time field and the times can cross both midnight and noon. As far as I can tell, there is no way to solve this without also supplying a date or am I missing something?
Regards,
BTJ
On Monday 29 September 2003 12:47, Bjørn T Johansen wrote: Well, I don't.. But normal timespan is about 6-7 hours +- (so one can assume max timespan = 12 hours really...)
Well, if you don't know what order you want, how can you tell PG to show them
in that order?
I think you might want to log a full timestamp by the sound of it. I'm not
sure your information is well defined.
--
Richard Huxton
Archonet Ltd
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)
Well I know the order I want!
The order should be like this during night time:
2230
2350
0100
0350
and
1030
1145
1230
1315
on day time...
But that was my initial question, "As far as I can tell, there is no way
to solve this without also supplying a date or am I missing something?"
BTJ
On Mon, 2003-09-29 at 15:22, Richard Huxton wrote: On Monday 29 September 2003 12:47, Bjørn T Johansen wrote: Well, I don't.. But normal timespan is about 6-7 hours +- (so one can assume max timespan = 12 hours really...)
Well, if you don't know what order you want, how can you tell PG to show them in that order?
I think you might want to log a full timestamp by the sound of it. I'm not sure your information is well defined.
=?ISO-8859-1?Q?Bj=F8rn?= T Johansen <bt*@havleik.no> writes: But that was my initial question, "As far as I can tell, there is no way to solve this without also supplying a date or am I missing something?"
You could possibly do it without, using some logic like this:
1. compute MAX(time) - MIN(time)
2. if less than 12 hours, assume no midnight wraparound, sort by
straight time.
3. if more than 12 hours, assume a wraparound, sort accordingly.
But it seems a heck of a lot easier and less error-prone to store
a full timestamp instead. What's your motivation for storing only
time, anyway? Not space savings --- the time and timestamp types
are both 8 bytes in PG.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org
On Monday 29 September 2003 20:19, Bjørn T Johansen wrote: Well I know the order I want!
The order should be like this during night time:
2230 2350 0100 0350
and
1030 1145 1230 1315
on day time... But that was my initial question, "As far as I can tell, there is no way to solve this without also supplying a date or am I missing something?"
And when does night-time end and day-time begin? If you have times:
0500 0600 0900 1200 1500 1900 2200 2300
is that the order or do you want
2200 2300 0500 0600 0900 1200 1500 1900
If you can tell me that, then the problem can be solved by the code I provided
earlier
--
Richard Huxton
Archonet Ltd
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
Well, I won't get times with a span like that....
On Mon, 2003-09-29 at 21:53, Richard Huxton wrote: On Monday 29 September 2003 20:19, Bjørn T Johansen wrote: Well I know the order I want!
The order should be like this during night time:
2230 2350 0100 0350
and
1030 1145 1230 1315
on day time... But that was my initial question, "As far as I can tell, there is no way to solve this without also supplying a date or am I missing something?"
And when does night-time end and day-time begin? If you have times: 0500 0600 0900 1200 1500 1900 2200 2300 is that the order or do you want 2200 2300 0500 0600 0900 1200 1500 1900
If you can tell me that, then the problem can be solved by the code I provided earlier
Yes, it would be a lot easier... But I can't do that, because the time
fields are default values; i.e. the time is the same every week but not
the date...
BTJ
On Mon, 2003-09-29 at 21:38, Tom Lane wrote: =?ISO-8859-1?Q?Bj=F8rn?= T Johansen <bt*@havleik.no> writes: But that was my initial question, "As far as I can tell, there is no way to solve this without also supplying a date or am I missing something?"
You could possibly do it without, using some logic like this: 1. compute MAX(time) - MIN(time) 2. if less than 12 hours, assume no midnight wraparound, sort by straight time. 3. if more than 12 hours, assume a wraparound, sort accordingly.
But it seems a heck of a lot easier and less error-prone to store a full timestamp instead. What's your motivation for storing only time, anyway? Not space savings --- the time and timestamp types are both 8 bytes in PG.
regards, tom lane
On Monday 29 September 2003 21:31, Bjørn T Johansen wrote: Well, I won't get times with a span like that....
OK - if you can explain what the rules are, I can tell you how to sort them.
From the information you've given it's impossible. I can't see any way that
you can decide that the right order is:
2200 2300 0200 0400
and not
0200 0400 2200 2300
I'm guessing there's some more information available you haven't mentioned
yet.
--
Richard Huxton
Archonet Ltd
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Why can't you just take my word for it, this is the way it should be
sorted....
The reason I know this, is because the timespan, as I have mention
before, from first record to last record is always less than 12 hours.
i.e the span from 0200 to 2200 is 20 hours, i.e. 2200 comes before 0200!
BTJ
On Tue, 2003-09-30 at 09:40, Richard Huxton wrote: On Monday 29 September 2003 21:31, Bjørn T Johansen wrote: Well, I won't get times with a span like that....
OK - if you can explain what the rules are, I can tell you how to sort them. From the information you've given it's impossible. I can't see any way that you can decide that the right order is: 2200 2300 0200 0400 and not 0200 0400 2200 2300
I'm guessing there's some more information available you haven't mentioned yet.
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org
On Tuesday 30 September 2003 09:00, Bjørn T Johansen wrote: Why can't you just take my word for it, this is the way it should be sorted....
I'm happy to take your word, but until I can figure out what rules you're
using I can't suggest anything. You clearly know what you want, but I can't
get to grips with precisely what that is.
The reason I know this, is because the timespan, as I have mention before, from first record to last record is always less than 12 hours. i.e the span from 0200 to 2200 is 20 hours, i.e. 2200 comes before 0200!
Ah - you said 24 hours originally, which means that all the examples we've
looked at were valid. Apologies if I missed the 12-hour bit.
So - your rule is something like:
For some block of times...
IF max(my_time) - min(my_time) > 12 hours
THEN sort "through midnight"
ELSE sort "naturally"
Which is what Tom said (I was wondering where he got his 12 hours from).
Have I got that right?
--
Richard Huxton
Archonet Ltd
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
On Tue, 2003-09-30 at 10:42, Richard Huxton wrote: So - your rule is something like:
For some block of times... IF max(my_time) - min(my_time) > 12 hours THEN sort "through midnight" ELSE sort "naturally"
Which is what Tom said (I was wondering where he got his 12 hours from). Have I got that right?
Yes, that sounds about right.. :)
But how do I code this in an Select statement?
BTJ
---------------------------(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
> Why can't you just take my word for it, this is the way it should be sorted....
He *does* take your word that this is the way it should be
sorted. But without knowing WHY this is the way it should be
sorted it is hard to deduce an algorithm for doing so.
What you probably need to do is sort them lowest -> highest
and then slide a window across the range (and wrapping the
lower times that leave the window) until the difference
between the then-first and then-last time is minimized. Even
then you can't know for sure unless you have additional
knowledge.
data 2 22 4 23
-> 2 4 22 23 -> delta-t 21 hours -> wrong order
-> 4 22 23 2 -> delta-t 22 hours -> wrong order
-> 22 23 2 4 -> delta-t 6 hours -> candidate
-> 23 2 4 22 -> delta-t 23 hours -> wrong order
here you can find your sort order by (delta-t < 12).
But what if there's another 1300 data point in there ?
2 4 13 22 23 21
4 13 22 23 2 22
13 22 23 2 4 15
22 23 2 4 13 15
23 2 4 13 22 23
I assume you are telling us that won't happen, right, i.e. it
is one of the rules ?
And what do you make of this sequence:
data 22 10
-> 10 22 12 -> candidate
-> 22 10 12 -> candidate
You't want to make sure delta-t is LESS than 12 hours.
I am sure there's an efficient algorithm out there to do this.
Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org
On Tuesday 30 September 2003 09:50, Bjørn T Johansen wrote: On Tue, 2003-09-30 at 10:42, Richard Huxton wrote: So - your rule is something like:
For some block of times... IF max(my_time) - min(my_time) > 12 hours THEN sort "through midnight" ELSE sort "naturally"
Which is what Tom said (I was wondering where he got his 12 hours from). Have I got that right?
Yes, that sounds about right.. :) But how do I code this in an Select statement?
Well, I'd write a function (notice the double-quoting):
-- sort_times(TARGET-TIME, DIFFERENCE)
-- Takes a target time and the difference max(t)-min(t) in its group
-- Returns a timestamp you can sort on
--
CREATE FUNCTION sort_times(time, interval)
RETURNS timestamptz AS '
SELECT
CASE
WHEN $2 > ''12:00''::interval AND $1<=''12:00:00''::time
THEN ''1970-01-02 00:00:00+00''::timestamptz + $1
ELSE ''1970-01-01 00:00:00+00''::timestamptz + $1
END
' LANGUAGE 'SQL' IMMUTABLE;
Then you have the wrong way:
SELECT
id, grp, ts
FROM
timetest
ORDER BY
grp, ts
;
id | grp | ts
----+-----+----------
1 | a | 11:00:00
2 | a | 14:00:00
3 | a | 17:00:00
4 | a | 20:00:00
7 | b | 01:00:00 ***
8 | b | 04:00:00 *** Oops - these are not
5 | b | 20:00:00 *** what we wanted
6 | b | 22:00:00 ***
9 | c | 03:00:00
10 | c | 06:00:00
11 | c | 08:00:00
(11 rows)
And the right way:
SELECT
t.id, t.grp, t.ts
FROM
timetest t,
(SELECT grp, max(ts)-min(ts) as tdiff FROM timetest GROUP BY grp) AS diffs
WHERE
t.grp = diffs.grp
ORDER BY
t.grp, sort_times(t.ts, diffs.tdiff)
;
id | grp | ts
----+-----+----------
1 | a | 11:00:00
2 | a | 14:00:00
3 | a | 17:00:00
4 | a | 20:00:00
5 | b | 20:00:00 ***
6 | b | 22:00:00 *** Ah - better!
7 | b | 01:00:00 ***
8 | b | 04:00:00 ***
9 | c | 03:00:00
10 | c | 06:00:00
11 | c | 08:00:00
(11 rows)
I'm not sure how PG will optimise the correctly sorted one - you'll have to
try it on your real data and see.
--
Richard Huxton
Archonet Ltd
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Oki, I will check it out....
Thx! :)
BTJ
On Tue, 2003-09-30 at 11:24, Richard Huxton wrote: On Tuesday 30 September 2003 09:50, Bjørn T Johansen wrote: On Tue, 2003-09-30 at 10:42, Richard Huxton wrote: So - your rule is something like:
For some block of times... IF max(my_time) - min(my_time) > 12 hours THEN sort "through midnight" ELSE sort "naturally"
Which is what Tom said (I was wondering where he got his 12 hours from). Have I got that right?
Yes, that sounds about right.. :) But how do I code this in an Select statement?
Well, I'd write a function (notice the double-quoting):
-- sort_times(TARGET-TIME, DIFFERENCE) -- Takes a target time and the difference max(t)-min(t) in its group -- Returns a timestamp you can sort on -- CREATE FUNCTION sort_times(time, interval) RETURNS timestamptz AS ' SELECT CASE WHEN $2 > ''12:00''::interval AND $1<=''12:00:00''::time THEN ''1970-01-02 00:00:00+00''::timestamptz + $1 ELSE ''1970-01-01 00:00:00+00''::timestamptz + $1 END ' LANGUAGE 'SQL' IMMUTABLE;
Then you have the wrong way:
SELECT id, grp, ts FROM timetest ORDER BY grp, ts ;
id | grp | ts ----+-----+---------- 1 | a | 11:00:00 2 | a | 14:00:00 3 | a | 17:00:00 4 | a | 20:00:00 7 | b | 01:00:00 *** 8 | b | 04:00:00 *** Oops - these are not 5 | b | 20:00:00 *** what we wanted 6 | b | 22:00:00 *** 9 | c | 03:00:00 10 | c | 06:00:00 11 | c | 08:00:00 (11 rows)
And the right way: SELECT t.id, t.grp, t.ts FROM timetest t, (SELECT grp, max(ts)-min(ts) as tdiff FROM timetest GROUP BY grp) AS diffs WHERE t.grp = diffs.grp ORDER BY t.grp, sort_times(t.ts, diffs.tdiff) ;
id | grp | ts ----+-----+---------- 1 | a | 11:00:00 2 | a | 14:00:00 3 | a | 17:00:00 4 | a | 20:00:00 5 | b | 20:00:00 *** 6 | b | 22:00:00 *** Ah - better! 7 | b | 01:00:00 *** 8 | b | 04:00:00 *** 9 | c | 03:00:00 10 | c | 06:00:00 11 | c | 08:00:00 (11 rows)
I'm not sure how PG will optimise the correctly sorted one - you'll have to try it on your real data and see.
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Phil Powell |
last post by:
I am having this problem: My PHP script will set a cookie, it's there in my
/Cookies folder. I delete the cookie (I have to for testing purposes, the
PHP script I run behaves according to this...
|
by: Neil Ginsberg |
last post by:
A while back I posted a message re. using an ADP file with a SQL Server back
end as opposed to MDB file with linked tables, thinking that the ADP file
would be less problematic. The input I got was...
|
by: George |
last post by:
In Time.h there is a structure defined for time settings. I'm
building an embedded system that has a Real Time Clock but it's not PC
compatible.
My question is:
I don't some elements of the...
|
by: KW |
last post by:
Hi all,
Appreciate if someone can help me out on this.
Currently, I have a tm structure holding information of the UTC time,
which is very likely to be in the past, meaning not the current...
|
by: Joseph Lee |
last post by:
Hi,
I am trying to track the time taken for a process to complete.
for loop //for multiple same Process
{
//Do Process
//Get Time
}
|
by: Shige |
last post by:
I have a .Net website hosted on another IIS6.0. Whenever I tried to
access my website after a long time, it will take forever to load, my
browser will just show a blank white page and the progress...
|
by: ruca |
last post by:
How can I show a run time clock in my ASP .NET page???? Can anyone have any
code to show that?
I have a javscript file that have all necessary code for that, but I'm using
VB in my ASPX page and I...
|
by: Oleg Leikin |
last post by:
Hi,
(newbie question)
I've created some simple .NET ASP application that should store cookies at the client machine.
According to the documentation cookie expiration time is set via...
|
by: Jeremy Sanders |
last post by:
Hi - I need to add support to a program for dates and times. The built-in
Python library seems to be okay for many purposes, but what I would like
would be Unix epoch style times (seconds relative...
|
by: Mikhail Kovalev |
last post by:
I'm using set_time_limit() to set maximum execution time. Is there a
way to check how much time is left at any time during the execution
itself?
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
| |