在传递Proc SQL中使用时间戳中的日期部分进行查询 [英] Querying using the datepart from a time stamp in a pass-through Proc SQL

问题描述

我想在下面的db2 pass-through proc SQL代码的where查询中使用时间戳的日期部分。我试过使用日期和日期部分功能，但它不会使用这种格式。有没有人知道下面使用相同代码的函数名称？

I am trying to use the date part of a time-stamp in my where query in a db2 pass-through proc SQL code below. I tried using date and datepart functions but it wont work with this format. Does anyone know the name of the function to use in the same code below?

PROC SQL; 
   connect to db2(ssid=smtng); 
     select *  from connection to db2 
         (select *  
             from ATable 
          where DATEPART(timestamp) > '12/01/2013'
   FOR READ ONLY WITH UR
    );
DISCONNECT FROM DB2;
QUIT; 

推荐答案

如果您在DB2中的datetime字段上使用函数那么数据库将无法使用它的索引（如果该字段已编入索引）。这是因为索引（几乎总是）在字段本身上创建，而不是由函数处理后的字段结果。这适用于大多数数据库，不仅仅是DB2。

If you use a function on the datetime field in DB2 then the database won't be able to use it's indexes (if that field is indexed). This is because indexes are (almost always) created on the field itself, not the result of the field after it has been processed by a function. This holds true for the majority of databases not just DB2.

相反，您想要做的是在一天的开始和结束时提供日期时间值并把它们放在一起。为了简化这个过程，我创建了一个名为 mysqldt。的格式。最初这种格式是针对mySQL数据库的，但是SQL服务器和DB2都使用相同的格式，所以它也可以用于这些格式：

Instead, what you want to do is supply datetime values for the beginning of the day and for the end of the day and get everything inbetween them. To simplify this process I created a format called mysqldt.. Originally this format was for a mySQL database, but SQL server and DB2 both use the same formats so it can be used on those as well:

proc format;
  picture mysqldt low-high = '''%Y-%0m-%0d %0H:%0M:%0S''' (datatype = datetime) ;
run ;

一旦这种格式可用，我倾向于使用宏变量。在我的程序的顶部，我将创建一个宏变量，指定在整个报告中使用的日期：

Once this format is available I tend to use macro variables. At the top of my program I would create a macro variable where I specify the date to use throughout the report:

%let rpt_date = %sysfunc(mdy(1,12,2013));

然后，我将创建两个日期时间字段，分别表示一天的开始和一天的结束，我将它们保存为SQL语句所需的格式：

I would then create two datetime fields representing the start of the day and the end of the day, and I would save them in the format that is needed for the SQL statement:

%let sql_start = %sysfunc(dhms(&rpt_date, 0, 0, 0), mysqldt.);
%let sql_end   = %sysfunc(dhms(&rpt_date,23,59,59), mysqldt.);

%put &rpt_date &sql_start &sql_end;

然后，您会将查询更改为如下所示：

You would then change your query to look like this:

proc sql; 
  connect to db2(ssid=smtng); 
  select *  from connection to db2 
         (select *  
          from atable 
          where timestamp between &sql_start and &sql_end
          for read only with ur
    );
quit; 

通过这种方式，您的查询不仅可以在您的查询中使用，而且SQL看起来更干净，如果你需要重新运行你的报告，你只需要在一个地方（在你的程序的顶部）更改报告日期。

This way, not only are your indexes now used in your query, but the SQL looks cleaner and reads easier, and you only need to change the report date in a single place (at the top of your program) if you need to rerun your report.

这篇关于在传递Proc SQL中使用时间戳中的日期部分进行查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！