1

I am trying to get a report between two date and I don't know What is the best way, I did a query but sometimes it doesn't work and I dont know where the error is.

Thank you again.

I am saving datetime in mysql field type varchar with php date('h:iA d-m-Y').

Example in mysql row date:

09:22AM 26-06-2015
08:00AM 27-06-2015
10:00PM 28-06-2015

When I use this data example $since=01-06-2015 $until=30-06-2015 this works

But if I use this data $since=01-06-2015 $until=01-07-2015 this doesn't work.

    $since = $_REQUEST['since'];
    $until = $_REQUEST['until'];

    mysql_query("select * from paradas where DATE_FORMAT(STR_TO_DATE(SUBSTR(date, 9),'%d-%m-%Y') , '%d-%m-%Y')  between '".$since."'  and '".$until."' ");
Eugen Rieck
  • 64,175
  • 10
  • 70
  • 92
user4131013
  • 425
  • 1
  • 7
  • 14

1 Answers1

1

By storing your date and time as a VARCHAR, instead as a DATETIME you also change the comparison between two rows from a datetime comparison (chronological) to a string comparison (lexigrafical).

If you now (as in your example) chose a string representation, where the lexigrafical comparison provides different results than the chronological comparison, you can no longer use contructs like BETWEEN or operators like <= and friends.

Solution: If you want to store date and time information, use the DATETIME column type.

In addition to that let me direct you to this SO question for a discussion of your SQL query.

EDIT

Just to make that clear:

In a lexigraphical order '01-06-2015' < '20-06-2015' < '30-06-2015', which is the same as the chronological order

But in a lexigraphical order '01-06-2015' < '01-07-2015' < '20-06-2015', which is contrary to chronological order.

Community
  • 1
  • 1
Eugen Rieck
  • 64,175
  • 10
  • 70
  • 92
  • How can I save this formater php date('h:iA d-m-Y'). 09:22AM 26-06-2015 in mysql. – user4131013 Aug 02 '15 at 21:59
  • You don't. You save a datetime, and when you query it, you format it as desired. – Eugen Rieck Aug 02 '15 at 22:16
  • Really I have timestamp and convert and save it in mysql field type varchar because I don't know how save it in MySQL field type datetime. – user4131013 Aug 02 '15 at 22:25
  • In what format do you have the date to insert ? From your PHP code I suspect it is either the current date and time (use MySQL `NOW()`) or some UNIX timestamp (use MySQL `FROM_UNIXTIME()`). – Eugen Rieck Aug 02 '15 at 22:49
  • Thank you Eugen for your response, yes my code is wide open to sql injection. I need you change it. I get timestamp from another aplication for example 1435326720 and I convert it $date = date('h:iA d-m-Y',1435326720); and save it in mysql. Any example how save it in field type datetime. – user4131013 Aug 03 '15 at 00:02
  • To insert a timestamp into a MySQL `DATETIME` field, just use `INSERT INTO ... SET field=FROM_UNIXTIME(1435326720), otherfield=...`. This will do the conversion while keeping chronological order perfectly intact. When you query the data use `SELECT ... DATE_FORMAT(field, '%H:%i%p %d-%m-%Y') AS pointintime, ...` – Eugen Rieck Aug 03 '15 at 00:19