473,322 Members | 1,493 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Time problem again?

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

Nov 12 '05 #1
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

Nov 12 '05 #2
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).


Nov 12 '05 #3
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)

Nov 12 '05 #4
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
Nov 12 '05 #5
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


Nov 12 '05 #6
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


Nov 12 '05 #7
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)

Nov 12 '05 #8
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.


Nov 12 '05 #9
=?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

Nov 12 '05 #10
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

Nov 12 '05 #11
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


Nov 12 '05 #12
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


Nov 12 '05 #13
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

Nov 12 '05 #14
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

Nov 12 '05 #15
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

Nov 12 '05 #16
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

Nov 12 '05 #17
> 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

Nov 12 '05 #18
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

Nov 12 '05 #19
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

Nov 12 '05 #20

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
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...
25
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...
14
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...
1
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...
7
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 }
2
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...
7
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...
15
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...
6
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...
6
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?
0
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...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
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...
1
isladogs
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...
0
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...
1
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...
1
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....
0
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
0
isladogs
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...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.