CODEWITHSUNDEEP
mysql
user1155299
user1155299

Reputation: 927

retrieving date in SELECT statement from mysql

I wrote the following mysql code:

select trade_dt, 
       ticker_id, 
       settle_price, 
       volume 
from hist 
where volume > 0 and trade_dt between '06/22/2011' and '06/30/2011';

but unfortunately, it returns dates outside the time window as follows:

+------------+------------+--------------+--------+
| trade_dt   | ticker_id  | settle_price | volume |
+------------+------------+--------------+--------+
| 06/23/2006 | N (Jul 06) |   156.900000 |     90 |
| 06/26/2006 | N (Jul 06) |   155.600000 |     63 |
| 06/27/2006 | N (Jul 06) |   159.300000 |     79 |
| 06/28/2006 | N (Jul 06) |   159.600000 |     57 |
| 06/29/2006 | N (Jul 06) |   143.400000 |    511 |
| 06/30/2006 | N (Jul 06) |   140.200000 |    342 |
| 06/23/2005 | V (Oct 05) |   151.200000 |     61 |
| 06/23/2011 | U (Sep 11) |    22.500000 |   6284 |
| 06/24/2011 | U (Sep 11) |    23.100000 |   4505 |
| 06/27/2011 | U (Sep 11) |    22.650000 |   3118 |
| 06/28/2011 | U (Sep 11) |    22.100000 |   3707 |
| 06/29/2011 | U (Sep 11) |    21.500000 |   5830 |
| 06/30/2011 | U (Sep 11) |    20.750000 |   9207 |
| 06/23/2008 | F (Jan 09) |    23.260000 |      2 |

and I wonder if that is because my trade_dt is defined as a string in hist table.EDITED table to replace char(10) with date

desc hist;
+-----------------+---------------+------+-----+---------+----------------+
| Field           | Type          | Null | Key | Default | Extra          |
+-----------------+---------------+------+-----+---------+----------------+
| futures_id      | int(11)       | NO   | PRI | NULL    | auto_increment |
| trade_dt        | date          | NO   |     | NULL    |                |
| ticker_id       | varchar(46)   | NO   | MUL | NULL    |                |
| settle_price    | decimal(10,6) | NO   |     | NULL    |                |
| change_in_price | decimal(10,6) | NO   |     | NULL    |                |
| volume          | bigint(11)    | NO   |     | NULL    |                |
| open_int        | bigint(11)    | NO   |     | NULL    |                |
+-----------------+---------------+------+-----+---------+----------------+

how do I fix my date problem?

ok, I changed the trade_dt field to date instead of char(10) and now when I run the below statement to load the data into the DB, it inserts blanks for dates, just because how the date is formatted in the data file.

LOAD DATA LOCAL INFILE '$fn' INTO TABLE $tn FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' (trade_dt,ticker_id,settle_price,change_in_price, volume, open_int);

this is a sample of raw data:

03/30/2012,Z (Dec 12),25.81,25.81,25.50,25.70,25.60,-0.45,24,0,318

Upvotes: 0

Views: 556

Answers (4)

Justin John
Justin John

Reputation: 9615

While saving data you get date as dd/mm/yyyy just explode the date and save as in correct format sql accepts (YYYY-MM-DD);

  $date = '26/07/2001';

   $data= explode("/",$date);

    $dateField = $data[2]."-".$data[1]."-".$data[0];

save this $dateField as date in sql table...

Upvotes: 0

rgafonso
rgafonso

Reputation: 56

The problem is really with string field type.
You have two options:

  • Change field to date (preferred)
  • Store string dates in 'YYYY/MM/DD' format

Upvotes: 0

kasavbere
kasavbere

Reputation: 6003

select trade_dt, ticker_id, settle_price, volume from 
hist where volume > 0 and trade_dt between '2011-06-22' and '2011-06-30';

MySQL retrieves and displays DATE values in 'YYYY-MM-DD' format.

Upvotes: 0

McGarnagle
McGarnagle

Reputation: 102783

As you note, it's because the data type is strings. You could get the correct result by casting the column to a date type inside the query, but that's incredibly inefficient. The only sound way to fix is to change the type of the trade_dt column, inside the table definition.

Upvotes: 1

Related Questions