帮助在 oracle 中使用时间戳值执行日期算术

发布于 2024-11-16 20:50:27 字数 672 浏览 6 评论 0原文

我需要一些帮助来构建一个 Oracle 查询，该查询将提取 2 天前的值。

timestamp 列（数字）值类似于 201106210000。其他日期（如数字）列的值如下： year=2011quarter=2month=6day=20

在哪里进行日期算术有意义？使用时间戳还是应该使用其他日历列？

该表还具有以下日期属性：

 Name                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 YEAR                           NUMBER
 QUARTER                        NUMBER
 MONTH                          NUMBER
 DAY                            NUMBER
 HOUR                           NUMBER
 TIMESTAMP                      NUMBER
 CNT_N                                          NUMBER

原文

I need some help in constructing an oracle query that will pull values for 2 days previous.

The timestamp column (number) values look like this 201106210000.
The other date like number columns have values like this:
year=2011 quarter=2 month=6 day=20

Where does it make sense to do the date arithmetic? using the timestamp or should I used these other calendar columns?

The table also has these date attributes:

 Name                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 YEAR                           NUMBER
 QUARTER                        NUMBER
 MONTH                          NUMBER
 DAY                            NUMBER
 HOUR                           NUMBER
 TIMESTAMP                      NUMBER
 CNT_N                                          NUMBER

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

找回味觉 2024-11-23 20:50:27

首先，这个表是一种糟糕的存储日期的方式（希望你知道这一点）。为什么不是 DATE 或 TIMESTAMP 列？

如果这是您无法控制的，我建议您在 YEAR、MONTH 和 DAY 列的串联上使用 to_date() ，以便您可以使用实际的 DATE 。类似于：

select *
from table
where to_date(year||'/'||month||'/'||day, 'YYYY/MM/DD') >= trunc(sysdate) - 2

由于您的列不是真实的日期列，因此您需要一个基于函数的索引来避免使用此方法进行全表扫描。

First, this table is a crappy way to store dates (which hopefully you are aware of). Why not either a DATE or TIMESTAMP column?

If that is out of your control, I would recommend using a to_date() on a concatenation of the YEAR, MONTH, and DAY columns so you have an actual DATE to work with. Something like:

select *
from table
where to_date(year||'/'||month||'/'||day, 'YYYY/MM/DD') >= trunc(sysdate) - 2

Since your columns aren't real date columns you would need a function-based index to avoid a full table scan using this method.

回复收藏 0 原文

若水般的淡然安静女子 2024-11-23 20:50:27

select * from TABLE1 

where
to_date( to_char(DAY,'00')   || 
         to_char(MONTH,'00') || 
         to_char(YEAR,'0000'), 'ddmmyyyy') < sysdate-2   
/* substitute sysdate-2 with any date but becareful of using EQUAL 
because sysdate will have a timestamp, so use Greater Than or LessThan */

但是，如果您也需要时间，这里是代码

select * from TABLE1 

where
to_date( 
to_char(DAY,'00') || 
to_char(MONTH,'00') ||
to_char(YEAR,'0000') || ' ' || 
to_char(HOUR,'00'), 'ddmmyyyy hh24') 
< sysdate-2   

/* I don't know if you have a field for minutes and seconds ,
 but I am sure you get the idea*/

select * from TABLE1 

where
to_date( to_char(DAY,'00')   || 
         to_char(MONTH,'00') || 
         to_char(YEAR,'0000'), 'ddmmyyyy') < sysdate-2   
/* substitute sysdate-2 with any date but becareful of using EQUAL 
because sysdate will have a timestamp, so use Greater Than or LessThan */

However, if you require time as well, here is the code

select * from TABLE1 

where
to_date( 
to_char(DAY,'00') || 
to_char(MONTH,'00') ||
to_char(YEAR,'0000') || ' ' || 
to_char(HOUR,'00'), 'ddmmyyyy hh24') 
< sysdate-2   

/* I don't know if you have a field for minutes and seconds ,
 but I am sure you get the idea*/

回复收藏 0 原文

~没有更多了~