CODEWITHSUNDEEP
phpmysqldate
John Spencer
John Spencer

Reputation: 380

MYSQL Date select between

I am using an API to collect some data from my client. however the date's of orders come in this format 06/09/2016 07:30PM UTC

at the moment I am just putting the data into my MYSQL table and storing it as Varchar,all fine for now but I will want to run some queries using select between (the range of dates)

I have tried changing the table structure so the column is Date instead of varchar but the dates come in as all 00000000's

I can return records using wild cards but not in a between range of dates.

Im thinking of trimming the data I don't need from the end then putting into a new table

Any suggestions on the best way to proceed would be appreciated.

Thanks.

Upvotes: 1

Views: 99

Answers (3)

Bernd Buffen
Bernd Buffen

Reputation: 15057

You can also use a virtual persistent Column like this. so you can store your date as string and MySQL direct convert it to DATETIME. So you can use the new field for BETWEEN and you can also create a INDEX on the new field

CREATE TABLE `dd` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `utc` VARCHAR(32) DEFAULT NULL,
  `nd` DATETIME AS (STR_TO_DATE(utc, '%d/%m/%Y %h:%i%p') ) persistent,
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

sample

MariaDB []> CREATE TABLE `dd` (
    ->   `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    ->   `utc` VARCHAR(32) DEFAULT NULL,
    ->   `nd` DATETIME AS (STR_TO_DATE(utc, '%d/%m/%Y %h:%i%p') ) persistent,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=INNODB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.30 sec)

MariaDB []> INSERT INTO `dd` (`utc`)
    -> VALUES
    -> ('06/09/2016 07:30PM UTC');
Query OK, 1 row affected, 1 warning (0.13 sec)

MariaDB []> select * from dd;
+----+------------------------+---------------------+
| id | utc                    | nd                  |
+----+------------------------+---------------------+
|  1 | 06/09/2016 07:30PM UTC | 2016-09-06 19:30:00 |
+----+------------------------+---------------------+
1 row in set (0.00 sec)

MariaDB []>

Upvotes: 0

nghtstr
nghtstr

Reputation: 41

There are a couple of things that I would strongly suggest.

Change the field to a DateTime

The reasoning behind this is that MySQL does a lot of optimizations when doing Date-math. Fortunately, you can still insert/update the field in the format of YYYY-MM-DD. However to get by your problem of converting VARCHAR into a valid DATETIME, I would do the following steps:

  1. Create a new field calling it something else.
  2. Do an UPDATE of your table, and save all of the old values into the new column. Ex:

UPDATE myTable SET newField = STR_TO_DATE(oldField, "%d/%m/%Y %h:%i%p");

  1. Drop the old column and rename the new one to the old name.

Like I said, MySQL does a lot of different optimizations to help with Date-math. This also allows you to do the second part of this...

BETWEEN is your friend

Using the keyword BETWEEN saves a lot of time, and it is also heavily optimized. So you can do the following very easily:

SELECT * FROM myTable WHERE myDate BETWEEN "2016-01-01" AND "2016-05-05";

Hope this helps!

Upvotes: 0

juergen d
juergen d

Reputation: 204904

Use

select str_to_date(date_column, '%d/%m/%Y %h:%i%p')
from your_table

to convert it to date: Example.

You could add another column of type datetime and put the values there. 

update your_table
set new_date_column = str_to_date(old_date_column, '%d/%m/%Y %h:%i%p')

Then drop the old column and rename the new one.

see here for how to format date strings.

Upvotes: 1

Related Questions