日期格式和SQL查询说明 [英] Date format and SQL Query clarification

问题描述

请考虑以下代码（在我上一个线程的上下文中在ColdFusion 8中对日期范围进行操作）。

 < cfparam name = Form.startdate default =＃dateformat（now（）-5，'mm / dd / yyyy'）＃> 
< cfparam name = Form.enddate default =＃dateformat（now（）-1，‘mm / dd / yyyy’）＃> 
< cfparam name = Form.selectdate default =＃dateformat（now（），‘mm / dd / yyyy’）＃> 
 
 
< cfinput type = dateField name = startdate label =开始日期 width = 100 value =＃Form.startdate＃> 
< cfinput type = dateField name = enddate label =结束日期 width = 100 value =＃Form.enddate＃> 
< cfinput name = submit type = submit value = Apply> 
< cfinput name =取消 type = submit value =下载CSV> 
  

我有以下问题：

1 ）当用户从日历中选择日期（开始日期）时，该日期是否会自动传递到下一行的值字段？

 < cfinput type = dateField name = startdate label =开始日期 width = 100 value =＃Form.startdate＃> 
  

如果上述内容是正确的，那么我相信同样的事情也将适用于结束日期。 / p>

2）关于SQL查询：

假设我正在编写以下查询（请注意， UpdatedDate 是我将从中提取 startdate和
enddate的列的名称：

 < cfquery数据源= XX.XX.X.XX name = qMyDatabase> 
 SELECT（SELECT count（* FROM MyDatabase）作为TOTAL_CONNECTIONS，
（SELECT count（*）从MyDatabase WHERE event_vc = OPEN并在＃Form.startdate＃和＃Form.enddate＃之间更新日期）作为OPEN_CONNECTIONS，
（选择计数（*）从FROM MyDatabase WHERE event_vc = BOUNCE并在#Form之间更新日期。 startdate＃和＃Form.enddate＃）作为BOUNCE_CONNECTIONS，
（从MyDatabase中选择count（*），其中event_vc = DEFERRED并在＃Form.startdate＃和＃Form.enddate＃之间更新日期。 AS DEFERRED_CONNECTIONS，
（从MyDatabase中选择count（*），其中event_vc = DELIVERED，并且UpdateDate在＃Form.startdate＃和＃Form.enddate＃之间。AS DELIVERED_CONNECTIONS，
（从MyDatabase，其中event_vc = DROPPED和UpdateDate在＃Form.startdate＃和＃Form.enddate＃之间。作为DROPPED_CONNECTIONS，
（从MyDatabase中选择count（*），其中event_vc = PROCESSED，并且UpdateDate在＃Form.startdate之间＃AND＃Form.enddate＃）作为PROCESSED_CONNECTIONS，
（ROUND（（SELECT OPEN_CONNECTIONS /（TOTAL_CONNECTIONS）* 100））作为 OPEN，
（ROUND（（SELECT DEFERRED_CONNECTIONS /（TOTAL_CONNECTIONS））） * 100））AS DEFERRED，
（ROUND（（SELECT DELIVERED_CONNECTIONS /（TOTAL_CONNECTIONS）* 100））AS DELIVERED，（ROUND（（SELECT DROPPED_CONNECTIONS /（TOTAL_CONNECTIONS））* 100））AS删除，（ROUND（（SELECT PROCESSED_CONNECTIONS /（TOTAL_CONNECTIONS））* 100））为 PROCESSED； 
< / cfquery> 
  

请告诉我以上查询是否正确？

3）关于日期格式。

如上所述，我使用的是掩码

mm / dd / yyyy

above，而我数据库中的列（UpdatedDate）使用不同的格式

（YYYY / MM / DD HH：MI：SS）

。
会造成任何问题吗？

解决方案

一个线程有很多问题..但是我将尝试解决其中的大多数问题。 （您可以尝试一下，就可以回答第一个问题；-）

就查询而言，即使它运行无误，也可以进行改进。我不会为您重写查询，但这是主要问题

• 首先，从不直接在SQL中使用原始客户端值。始终使用 cfqueryparam 防止SQL注入。它还有其他好处，但是在Web应用程序中至关重要。




• 第二，您传入了 date字符串 。日期字符串不明确，可能会误解，具体取决于进行解析的格式和工具。最好使用日期 objects 代替。一种方法是使用 cfqueryparam 和以下日期类型之一： cf_sql_date （仅日期）或 cf_sql_timestamp （日期和时间）。




• 第三，正如我在您的其他线程，您确实需要简化查询！这么多子查询已经非常笨拙了..为每个子查询添加日期过滤器使其彻底变得难以管理。我建议您寻找简化方法。 Ed的建议提供了一种可能性，将其简化为单个 JOIN 和一些函数调用。

列（UpdatedDate）使用 YYYY / MM / DD HH：MI：SS

实际上，这正是IDE 的显示方式和时间这一事实。

说您想检索6月日期的所有记录：

  form.startDate = 06/01/2013​​ 
 form.endDate = 06/30/2013​​ 
  

从概念上讲，您将需要这样的sql表达式：

 在哪列之间 2013年6月1日午夜和 2013年6月30日晚上11:59:59 
  

但是，构造这些日期/时间值有点笨拙的IMO。一种简单的处理方式是使用以下范例：

  WHERE列> = {startDateAtMidnight} 
 AND列< ; {dayAfterEndDateAtMidnight} 
  

您的实际查询过滤器看起来像这样：

  WHERE列> =< cfqueryparam value =＃form.startDate＃ 
 cfsqltype = cf_sql_date> 
 AND列< < cfqueryparam value =＃dateAdd（’d，1，form.endDate）＃ 
 cfsqltype = cf_sql_date> 
  

通过在 form.endDate 中添加一天，并使用< 比较，结果查询为：

  WHERE列> ='2013-06-01 00:00:00'
 AND列< '2013-07-01 00:00:00'
  

这将产生与早于BETWEEN表达式。

Please consider the following piece of code ( In the context of my previous thread Playing around with date range in ColdFusion 8).

<cfparam name="Form.startdate" default="#dateformat(now()-5, 'mm/dd/yyyy')#">
<cfparam name="Form.enddate" default="#dateformat(now()-1, 'mm/dd/yyyy')#">
<cfparam name="Form.selectdate" default="#dateformat(now(), 'mm/dd/yyyy')#">


<cfinput type="dateField" name="startdate" label="Start Date" width="100" value="#Form.startdate#">
<cfinput type="dateField" name="enddate" label="End Date" width="100" value="#Form.enddate#">
<cfinput name="submit" type="submit" value = "Apply">
<cfinput name="cancel" type="submit" value="Download CSV">

I have following questions:

1) When the user will select a date(startdate) from the calender, is the date automatically going to pass to the "value" field of the following line?

 <cfinput type="dateField" name="startdate" label="Start Date" width="100" value="#Form.startdate#">

If the above is true then I believe the same thing will apply with the end date as well.

2) Regarding the SQL Query:

Suppose I'm writing the following query(Please note that "UpdatedDate" is the name of the column from where I'll be pulling up the "startdate" and "enddate":

             <cfquery datasource = "XX.XX.X.XX" name="qMyDatabase">
            SELECT(SELECT count(*) FROM MyDatabase) AS TOTAL_CONNECTIONS,
            (SELECT count(*) FROM MyDatabase WHERE event_vc = "OPEN" AND UpdatedDate BETWEEN #Form.startdate# AND #Form.enddate#  ) AS OPEN_CONNECTIONS,
            (SELECT count(*)FROM MyDatabase WHERE event_vc = "BOUNCE"    AND  UpdatedDate    BETWEEN #Form.startdate# AND #Form.enddate#) AS BOUNCE_CONNECTIONS,
            (SELECT count(*) from MyDatabase where event_vc = "DEFERRED" AND  UpdatedDate    BETWEEN #Form.startdate# AND #Form.enddate#) AS DEFERRED_CONNECTIONS,
            (SELECT count(*) from MyDatabase where event_vc = "DELIVERED" AND UpdatedDate    BETWEEN #Form.startdate# AND #Form.enddate#) AS DELIVERED_CONNECTIONS,
            (SELECT count(*) from MyDatabase where event_vc = "DROPPED"  AND  UpdatedDate    BETWEEN #Form.startdate# AND #Form.enddate#) AS DROPPED_CONNECTIONS,
            (SELECT count(*) from MyDatabase where event_vc = "PROCESSED" AND UpdatedDate    BETWEEN #Form.startdate# AND #Form.enddate#) AS PROCESSED_CONNECTIONS,
(ROUND((SELECT OPEN_CONNECTIONS / (TOTAL_CONNECTIONS))*100)) AS "OPEN",
(ROUND((SELECT DEFERRED_CONNECTIONS / (TOTAL_CONNECTIONS))*100)) AS "DEFERRED",
(ROUND((SELECT DELIVERED_CONNECTIONS / (TOTAL_CONNECTIONS))*100)) AS "DELIVERED", (ROUND((SELECT DROPPED_CONNECTIONS / (TOTAL_CONNECTIONS))*100)) AS "DROPPED", (ROUND((SELECT PROCESSED_CONNECTIONS / (TOTAL_CONNECTIONS))*100)) AS "PROCESSED";
 </cfquery>

Please let me know if the above query seems to be correct?

3) Regarding the Date Format.

As mentioned above, I'm using the mask

mm/dd/yyyy

above , while the column(UpdatedDate) in my database uses different format

(YYYY/MM/DD HH:MI:SS)

. Is it going to create any problems?

解决方案

That is a lot of questions for one thread .. but I will try and address most of them. (The first question you can answer yourself, just by trying it ;-)

As far as the query, even if it ran without error - it can be improved. I am not going to rewrite the query for you, but here are the main issues

• First, never use raw client values directly in SQL. Always use cfqueryparam to protect against sql injection. It has other benefits as well, but that one is critical in a web application.

• Second, you are passing in date strings. Date strings are ambiguous and can be misinterpreted, depending on the format and tool doing the parsing. It is much better use date objects instead. One way to do that is by using cfqueryparam and one of the date types: cf_sql_date (date only) or cf_sql_timestamp (date and time).

• Third, as I mentioned on your other thread, you really need to simplify your query! That many subqueries is already unwieldy .. adding date filters to each subquery makes it downright unmanageable. I would recommend looking at ways to simplify it. Ed's suggestion offered one possibility, by reducing it to a single JOIN and a few function calls.

the column (UpdatedDate) uses YYYY/MM/DD HH:MI:SS

Well actually that is just how your IDE displays it to humans. It is not really stored that way. Internally, dates are stored as big numbers. However, your query does need to account for the fact that your column stores a date and time.

Say you wanted to retrieve all records dated in June:

    form.startDate = "06/01/2013"
    form.endDate = "06/30/2013"

Conceptually, you would need a sql expression like this:

    WHERE column BETWEEN '06/01/2013 at midnight' AND '06/30/2013 11:59:59 PM' 

However, constructing those date/time values is a bit klunky IMO. A simpler way to handle it is using this paradigm:

   WHERE column >= {startDateAtMidnight}        
   AND   column <  {dayAfterEndDateAtMidnight}

Your actual query filter would look something like this:

    WHERE column >= <cfqueryparam value="#form.startDate#" 
                                  cfsqltype="cf_sql_date">
    AND   column <  <cfqueryparam value="#dateAdd('d', 1, form.endDate)#" 
                                  cfsqltype="cf_sql_date">

By adding one day to form.endDate, and using a < comparison, the resulting query is:

    WHERE column >= '2013-06-01 00:00:00'  
    AND   column < '2013-07-01 00:00:00'  

This will produce the exact same results as the earlier BETWEEN expression.

这篇关于日期格式和SQL查询说明的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！