我需要一个SQL ... [英] I need a SQL...

问题描述

我需要编写一个SQL来计算开始时间

和停止时间之间的间隔。这是简单的部分。问题是我的时间部分只有

，即没有日期，所以如果开始时间是在午夜之前，我怎么能确定计算

的间隔时间。停止时间是在午夜后的
之后？

问候，


BTJ


-

---------------------------------------- -------------------------------------------------- -----

Bj？rn T Johansen（BSc，MNIF）

执行经理
bt*@havleik.no Havleik Consulting

电话：[phone] Conradisvei 4

传真：[phone] N- 1338 Sandvika

Cellular：[phone] http：//www.havleik .no

------------------------------------ -------------------------------------------------- ---------

盒子旁边的贴纸说支持的平台：Windows
98，Windows NT 4.0，

----- -------------------------------------------------- ----------------------------------------

I need to write a SQL that calculates the interval between a start time
and a stop time. This is the easy part. The problem is that I only have
the time part, i.e. no date, so how can I be sure to also calculate the
interval if the start time is before midnight and the stop time is after
midnight?
Regards,

BTJ

--
-----------------------------------------------------------------------------------------------
Bj?rn T Johansen (BSc,MNIF)
Executive Manager
bt*@havleik.no Havleik Consulting
Phone : [phone] Conradisvei 4
Fax : [phone] N-1338 Sandvika
Cellular : [phone] 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."
-----------------------------------------------------------------------------------------------

推荐答案

解决方案：


SELECT启动时间，停止时间，（停止时间 - 启动时间> = 0然后停止时间 - 启动时间ELSE停止时间 - 启动时间+'' 24小时'结束'作为timediff来自mytable;


/ Mattias


-----原始消息-----

来自：Bj？rn T Johansen

致：Andrew L. Gould

Cc：PostgreSQL一般清单

已发送：2003年9月11日星期四下午2:12

主题：回复：[一般]我需要一个SQL ...

On Thu，2003-09-11 at 14:07，Andrew L. Gould写道：

2003年9月11日星期四上午06:25，Bj？rn T Johansen写道：

Solution:

SELECT starttime, stoptime, (CASE WHEN stoptime-starttime >= 0 THEN stoptime-starttime ELSE stoptime-starttime+''24 hours'' END) as timediff FROM mytable;

/Mattias

----- Original Message -----
From: Bj?rn T Johansen
To: Andrew L. Gould
Cc: PostgreSQL general list
Sent: Thursday, September 11, 2003 2:12 PM
Subject: Re: [GENERAL] I need a SQL...
On Thu, 2003-09-11 at 14:07, Andrew L. Gould wrote:
On Thursday 11 September 2003 06:25 am, Bj?rn T Johansen wrote:

我需要写一个SQL计算恒星之间的间隔t时间
和停止时间。这是简单的部分。问题是我只有时间部分，即没有日期，所以如果开始时间在午夜之前且停止时间在
间隔？ >午夜？

问候，

BTJ

I need to write a SQL that calculates the interval between a start time
and a stop time. This is the easy part. The problem is that I only have
the time part, i.e. no date, so how can I be sure to also calculate the
interval if the start time is before midnight and the stop time is after
midnight?
Regards,

BTJ

如果你测量的活动或时期可以等于或超过12小时，你没有开始日期和停止日期，你不能可靠地计算它。

如果期限总是少于12小时（并且你假设所有的数据都是好的b $ b b b b b b b b that that that that that that that that that that that that that that that that that that that that that that that that b $ b日期不必与时间在同一个字段中，因为您可以添加

日期和时间数据来创建日期时间计算的时间戳：


（stop_date + stop_time） - （start_date + start_time）


祝你好运，


Andrew Gould


是的，期间c超过12个小时，所以你说的是没有日期部分这不可能解决

？我可以在我的业务逻辑中编写这个逻辑，但我希望在我的数据库层中解决这个问题...

BTJ

If the activity or period you are measuring can equal or exceed 12 hours, you
won''t be able to calculate it reliably without a start date and a stop date..
If the periods are always less than 12 hours (and you assume all the data is
good), then stop times that are less than start times would indicate an
intervening midnight.

The dates do not have to be in the same fields as the times, since you can add
date and time data to create a timestamp for datetime calculations:

(stop_date + stop_time) - (start_date + start_time)

Best of luck,

Andrew Gould

Yes, the period can exceed 12 hours, so what you are saying is that this isnot possible to solve
without the date part? I can write this logic in my business logic but I was hoping to
solve this in my database layer...
BTJ

2003年9月11日星期四上午06:25，Bj？rn T Johansen写道：

On Thursday 11 September 2003 06:25 am, Bj?rn T Johansen wrote:

我需要编写一个SQL来计算一个开始时间间隔
和停止时间。这是简单的部分。问题是我只有时间部分，即没有日期，所以如果开始时间在午夜之前且停止时间在
间隔？ >午夜？

问候，

BTJ

I need to write a SQL that calculates the interval between a start time
and a stop time. This is the easy part. The problem is that I only have
the time part, i.e. no date, so how can I be sure to also calculate the
interval if the start time is before midnight and the stop time is after
midnight?
Regards,

BTJ

如果你测量的活动或时期可以等于或超过12小时，你没有开始日期和停止日期，你不能可靠地计算它。

如果期限总是少于12小时（和你假设所有数据都是好的b $ b b b b b b b b that that that that that that that that that that that that that that that that that that that that that that that that that that $ b日期不必与时间在同一个字段中，因为您可以添加

日期和时间数据来创建日期时间计算的时间戳：


（stop_date + stop_time） - （start_date + start_time）


祝你好运，


Andrew Gould


------------------------- - （播出结束）---------------------------

提示8：解释分析是你的朋友

If the activity or period you are measuring can equal or exceed 12 hours, you
won''t be able to calculate it reliably without a start date and a stop date.
If the periods are always less than 12 hours (and you assume all the data is
good), then stop times that are less than start times would indicate an
intervening midnight.

The dates do not have to be in the same fields as the times, since you can add
date and time data to create a timestamp for datetime calculations:

(stop_date + stop_time) - (start_date + start_time)

Best of luck,

Andrew Gould

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

嗯，它很接近...... :)


但它看起来像是没有工作..

如果我运行你的sql，timediff是负面的。


但如果我运行这个：

SELECT（stoptime-starttime +''24小时'）作为timediff来自mytable

timediff具有正确的值..


你看到任何错误吗？这个案子，因为我不是吗？

BTJ


On Thu，2003-09-11 at 14：29，Mattias Kregert写道：

Well, it''s close... :)

But it looks like the case doesn''t work..
If I run your sql, the timediff is negative.

But if I run this:
SELECT (stoptime-starttime+''24 hours'') as timediff FROM mytable
the timediff has correct value..

Do you see any error in the case, cause I don''t?
BTJ

On Thu, 2003-09-11 at 14:29, Mattias Kregert wrote:

解决方案：

SELECT starttime，stoptime，（例如，停止时间 - 启动时间> = 0那么
停止时间 - 启动时间ELSE停止时间 - 启动时间+'''24小时'结束）作为timediff
来自mytable;

/ Mattias

-----原帖-----
来自：Bj？rn T约翰森
致：Andrew L. Gould
Cc ：PostgreSQL一般列表
发送时间：2003年9月11日星期四下午2:12
主题：回复：[一般]我需要一个SQL ...

2003年4月-09-11在14:07，Andrew L. Gould写道：

Solution:

SELECT starttime, stoptime, (CASE WHEN stoptime-starttime >= 0 THEN
stoptime-starttime ELSE stoptime-starttime+''24 hours'' END) as timediff
FROM mytable;

/Mattias

----- Original Message -----
From: Bj?rn T Johansen
To: Andrew L. Gould
Cc: PostgreSQL general list
Sent: Thursday, September 11, 2003 2:12 PM
Subject: Re: [GENERAL] I need a SQL...

On Thu, 2003-09-11 at 14:07, Andrew L. Gould wrote:

> 2003年9月11日星期四上午06:25，Bj？rn T Johansen写道：

> On Thursday 11 September 2003 06:25 am, Bj?rn T Johansen wrote:

> >我需要编写一个SQL来计算开始时间之间的间隔
> >和停止时间。这是简单的部分。问题是我只有
> >时间部分，即没有日期，所以我怎么能确定也计算
> >如果开始时间在午夜之前且停止时间在之后，则为间隔
> >午夜？
> >
> >
> >问候，
> >
> > BTJ

> > I need to write a SQL that calculates the interval between a start time
> > and a stop time. This is the easy part. The problem is that I only have
> > the time part, i.e. no date, so how can I be sure to also calculate the
> > interval if the start time is before midnight and the stop time is after
> > midnight?
> >
> >
> > Regards,
> >
> > BTJ

>
>如果您测量的活动或时间段等于或超过12小时，则
>没有开始日期和停止日期，将无法可靠地计算出来。
>如果周期总是少于12小时（并假设所有数据都是好的），那么小于开始时间的停止时间将表示
>干预午夜。
>
>日期不必与时间在同一个字段中，因为您可以添加
>用于创建日期时间计算时间戳的日期和时间数据：
>
> （stop_date + stop_time） - （start_date + start_time）
>
>祝你好运，
>
>安德鲁古尔德

>
> If the activity or period you are measuring can equal or exceed 12 hours, you
> won''t be able to calculate it reliably without a start date and a stop date.
> If the periods are always less than 12 hours (and you assume all the data is
> good), then stop times that are less than start times would indicate an
> intervening midnight.
>
> The dates do not have to be in the same fields as the times, since you can add
> date and time data to create a timestamp for datetime calculations:
>
> (stop_date + stop_time) - (start_date + start_time)
>
> Best of luck,
>
> Andrew Gould

是的，期限可能超过12小时，所以你说的是没有日期部分这是不可能解决的？我可以在我的业务逻辑中编写这个逻辑，但我希望在我的数据库层中解决这个问题...

BTJ

Yes, the period can exceed 12 hours, so what you are saying is that this is not possible to solve
without the date part? I can write this logic in my business logic but I was hoping to
solve this in my database layer...
BTJ

- -------------------------（播出结束）-------------------- -------

提示4：不要杀死-9''邮政局长

---------------------------(end of broadcast)---------------------------
TIP 4: Don''t ''kill -9'' the postmaster

这篇关于我需要一个SQL ...的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！