Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2015
    Posts
    1

    Unanswered: Select query not selecting date range as provided in where clause

    MySQL: Server version: 5.1.69 Source distribution
    Operating System: Linux 2.6.32-358.14.1.el6.x86_64 x86_64

    I have following table structure

    CREATE TABLE `some_table` (
    `some_table_id` int(20) NOT NULL AUTO_INCREMENT,
    `from_number` varchar(60) DEFAULT NULL,
    `to_number_proxy` varchar(100) DEFAULT NULL,
    `to_number` varchar(60) DEFAULT NULL,
    `datetime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `body` varchar(1000) NOT NULL,
    `status` int(20) DEFAULT '0',
    `office_id` int(20) DEFAULT NULL,
    `customer_type` varchar(100) DEFAULT NULL,
    `client_id` int(11) DEFAULT NULL,
    `campaign_id` int(11) DEFAULT NULL,
    `customer_name` varchar(255) DEFAULT NULL,
    PRIMARY KEY (`incoming_text_id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

    I am running the following query to get records within a specific date range. However, the results produced by this query is not correct as the records selected are not within the date range specified. The date range specified was between 1st July 2015 and 3rd July 2015 but the results is showing sendReceiveDatetime from 20th March 2014 and 27th March 2014. It does give a few records from the selected date range too.

    Could you please help understanding what may be the issue? Is it the code below, or the character setting or may be a MySQL bug that is causing this.

    SELECT
    i.some_table_id AS Id,
    i.office_id AS officeId,
    i.campaign_id AS campaignId,
    i.customer_name AS customerFullName,
    IF(i.customer_type = 'Gold',i.from_number,i.to_number_proxy) AS goldNumber,
    IF(i.customer_type = 'Bronze',i.from_number,i.to_number_proxy) AS bronzeNumber,
    i.body AS body,
    i.datetime AS sendReceiveDatetime,
    IF(i.customer_type = 'Bronze','IN','OUT') AS direction,
    i.status AS someStatus,
    IF(i.status = 0, 'true', 'false') AS unread
    FROM
    some_table i
    WHERE
    STR_TO_DATE(DATE_FORMAT(i.datetime,'%Y-%m-%d'),'%Y-%m-%d') >= STR_TO_DATE('2015-07-01', '%Y-%m-%d')
    AND STR_TO_DATE(DATE_FORMAT(i.datetime,'%Y-%m-%d'),'%Y-%m-%d') <= STR_TO_DATE('2015-07-03', '%Y-%m-%d')
    AND i.office_id IN (11011 , 12022, 13033)
    AND (i.from_number = '1234567890' AND i.customer_type = 'Gold')
    OR (i.to_number_actuallysend = '1234567890' AND i.customer_type = 'Bronze');

  2. #2
    Join Date
    Sep 2007
    Location
    Bangalore India
    Posts
    28
    Basically time-stamp store the data in date & time format, so whenever you are fetching the data.
    Use date_field between '2015-07-01 00:00:00' and '2015-07-01 23:59:59'

    So the query will fetch the data between the date range, also using date_format and str_to_data is not a proper way.
    Which will affect the performance in query side.

    I have modified your query please check it.
    SELECT
    i.some_table_id AS Id,
    i.office_id AS officeId,
    i.campaign_id AS campaignId,
    i.customer_name AS customerFullName,
    IF(i.customer_type = 'Gold',i.from_number,i.to_number_proxy) AS goldNumber,
    IF(i.customer_type = 'Bronze',i.from_number,i.to_number_proxy) AS bronzeNumber,
    i.body AS body,
    i.datetime AS sendReceiveDatetime,
    IF(i.customer_type = 'Bronze','IN','OUT') AS direction,
    i.status AS someStatus,
    IF(i.status = 0, 'true', 'false') AS unread
    FROM
    some_table i
    WHERE i.datetime between '2015-07-01 00:00:00' AND '2015-07-03 23:59:59'
    AND i.office_id IN (11011 , 12022, 13033)
    AND (i.from_number = '1234567890' AND i.customer_type = 'Gold')
    OR (i.to_number_actuallysend = '1234567890' AND i.customer_type = 'Bronze');

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •