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

问题描述

我正在尝试在下面的 db2 pass-through proc SQL 代码中的 where 查询中使用时间戳的日期部分.我尝试使用 date 和 datepart 函数，但它不适用于这种格式.有谁知道下面相同代码中要使用的函数的名称?

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 Server 和 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屋！