时间问题了吗？ [英] Time problem again?
问题描述
我需要根据时间字段对某些数据进行排序，时间可以在午夜和中午交叉。据我所知，没有办法解决
这个没有提供日期或者我错过了什么？
问候，
BTJ
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
推荐答案
2003年9月29日星期一10:11，Bj ?? rn T Johansen写道：
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?
你不要当你的期间说开始。这让我处于同样的位置。
PostgreSQL - 我不能告诉你03:00代表你期间的早期或晚期
时间。 />
我认为你有一个期间从例如开始的情况06:00:00和
一直持续到第二天05:59:59。
您可以这样做：
SELECT
my_time，
CASE
WHEN my_time< ''06：00：00''那么my_time +''18小时''::间隔
ELSE my_time - ''6小时''::间隔
结束
AS sort_time
FROM
time_table
ORDER BY
sort_time
那会翻译：
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
如果你愿意，你可以把它包装在一个SQL函数中，或者甚至在函数上添加一个索引
（但检查一下手册详情如何）。
-
Richard Huxton
Archonet Ltd
---------------------------（广播结束）---------------- -----------
提示4：不要杀死-9''邮政局长
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
问题是我没有这样的期间。我可以选择
包含这些数据：
2350
0110
0330
然后应该这样排序。
我还可以拥有以下内容：
1030
1145
1240
（也按照所示排序......）
我唯一知道的是，第一张
记录和最后一张记录之间的间隔总是不到24小时...
BTJ
星期一，2003-09-29 13:21，Richard Huxton写道：
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:
2003年9月29日星期一10： 11，Bj？Tn Johansen写道：
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?
你什么时候不说;周期"开始。这使我处于和PostgreSQL相同的位置 - 我无法告诉你03:00是否代表你们这段时期的早期或晚期时间。
我认为你有一个时期从例如开始的情况06:00:00和
一直持续到第二天05:59:59。
你可以这样做：
SELECT
my_time，
CASE
当my_time< ''06：00：00''然后my_time +''18小时''::间隔
ELSE my_time - ''6小时''::间隔
END
AS sort_time FROM
time_table
ORDER BY
sort_time
那会翻译：
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
如果你愿意，你可以将它包装在SQL函数中，甚至可以在函数上添加索引（但请查看手册以了解详细信息）。
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).
2003年9月29日星期一12：26，Bj？rn T Johansen写道：
On Monday 29 September 2003 12:26, Bj?rn T Johansen wrote:
问题是我没有这样一个时期。我可以选择一个包含这些数据的选项：
2350
0110
0330
然后应该这样排序。 />
我还可以得到以下内容：
1030
1145
1240
（也如图所示排序......）
我唯一知道的是，第一个
记录和最后一个记录之间的间隔总是不到24小时......
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...
你怎么知道第一个例子不应该是
0110
0330
2350 < br $> b $ b -
Richard Huxton
Archonet Ltd
--------- ------------------（广播结束）---------------------------
提示2：您可以使用取消注册命令一次性取消所有列表
（发送取消注册YourEmailAddressHere到 ma ******* @ postgresql.org ）
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)
这篇关于时间问题了吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
