Data Cleansing Notes: "Time period" data get the habit of careful wrong

Original works, from the "Blue Blog" blog, Welcome to reprint, please be sure to indicate the source, otherwise the right to pursue copyright legal responsibility.

Deep Blue Blog:http://blog.csdn.net/huangyanlong/article/details/46453391

Background

In a project, there are business requirements for querying the time period, and theJava code is similar to the following:

<if test= "Enddate!=null" >and bankjlsj_bankjlsjssj<! [Cdata[<=]]>to_date (#{enddate}, ' Yyyy-mm-dd ') </if><if test= "Startdate!=null" >and BANKJLSJ_ bankjlsjssj<! [cdata[>=]]> to_date (#{startdate}, ' Yyyy-mm-dd ') </if>

In the project construction, the developer discovers that the query time period has the leakage data phenomenon, when the date is accurate to month day time the data can be able to view, but if the time is accurate to the minute minute type the data will be missing.

Solution

This is the experience of learning a developer, changing the code above to read as follows:

<if test= "Enddate!=null" >and bankjlsj_bankjlsjssj<! [Cdata[<]]>to_date (#{enddate}, ' Yyyy-mm-dd ') +1</if><if test= "Startdate!=null" >and BANKJLSJ_ bankjlsjssj<! [cdata[>=]]> to_date (#{startdate}, ' Yyyy-mm-dd ') </if>

This will not only be able to query such as "2014-09-02" Such data, you can also query "2014-09-02 12:23:08" such data.

Experimental

Constructs an experimental environment.

Createtabletest (IDVARCHAR2 (+) default Sys_guid (), BANKJLSJ_BANKJLSJSSJ date);

Insert two experimental data.

Insertintotest (BANKJLSJ_BANKJLSJSSJ) VALUES (to_date (20150516, ' yyyy-mm-dd '); Insertintotest (BANKJLSJ_BANKJLSJSSJ VALUES (to_date (20150516120214, ' Yyyy-mm-dd hh24:mi:ss ')); commit;

To test the query results, proceed as follows

Check the data in the Test table First, as follows:

Case one: Using the initial >= and <= conditions above, query the data for the time period of the 20150516 Day, and return a result of the data as follows:

A little modification, and then to inquire about,20150516 Day time period of data, query as follows:

You can see that the second query results are correct, and use the scope of learning to use the "+1" is the correct way, for the time period statistics of the general usage should not be taken for granted by two equals number to divide the upper and lower bounds, but should use the correct interval to query.

Small knowledge, simple and remember.

The Blue Growth Kee series _20150523*************************************

original works, from the "Blue Blog" blog, Welcome to reprint, please be sure to indicate the source ( Http://blog.csdn.net/huangyanlong ).

The growth of Blue-chase DBA (1): Rushing to the road and into Shandong

Blue Growth Kee-Chase DBA (2): Install! Installation! A long-lost memory that caused me to re-perceive the DBA

The growth of Blue-Chase DBA (3): antique operation, data import and export become a problem

The growth of Blue-Chase DBA (4): Recalling the youth, and then explore Oracle installation (Linux 10g, 11g)

The growth of Blue-Chase DBA (5): No talking about business, annoying application system

The growth of Blue-Chase DBA (6): Work and life: small skills, great people

The growth of Blue-Chase DBA (7): Basic Command, foundation stone

The growth of Blue-chase DBA (8): Regain SP report, recall Oracle's Statspack experiment

The growth of Blue-Chase DBA (9): National Day, chasing DBA, new plan, new departure

The growth of the Blue-Chase DBA (10): flying knives to defend themselves, not expertise: fiddling with middleware WebSphere

The growth of Blue-chase DBA (11): The ease of coming home, the dizzy wake up

The growth of Blue-chase DBA (12): Seven days seven harvested SQL

The growth of blue-chasing DBAs (13): Coordinating hardware vendors, six stories: what you see and feel "servers, storage, switches ..."

The growth of Blue-chase DBA (14): An unforgettable "cloud" side, starting Hadoop deployment

The growth of Blue-Chase DBA (15): Think FTP is very "simple", who chengxiang twists

The growth of Blue-Chase DBA: The DBA also drank and was 捭阖

******************************************************************************************************* ***********

Soccer and Oracle Series _20150528***********************************

original works, from the "Blue Blog" blog, Welcome to reprint, please be sure to indicate the source ( Http://blog.csdn.net/huangyanlong ).

Soccer and Oracle Series (1): 32 Way Princes soldiers of, Oracle32 Process Alliance Group A bigger picture of the Brazilian Smon process

Soccer and Oracle Series (2): Brazil opener Preview, Oracle architecture Talk

Soccer and Oracle Series (3): Oracle process ranking, World Cup round is about to be fought!

Soccer with Oracle Series (4): From Brazil fiasco in Germany, think of the difference in RAC topology comparison!  

Soccer and Oracle Series (5): Fifa14 game The missing DirectX Library is analogous to the RPM package from Oracle!

Soccer and Oracle Series (6): The Asian Cup with the library--Refueling Chinese team

****************************************************************************************************** ************

Data Cleansing Notes: "Time period" data get the habit of careful wrong