is this the most efficient way to do this?

[ahs10]

so i have a table that contains a column of timestamps and i want the user to be able to provide a to and from date to search the table, for rows between the two dates.

 

$query = "SELECT * FROM records WHERE record_timestamp >= STR_TO_DATE('" . $from . " 00:00:00', '%c/%e/%Y %T') AND record_timestamp <= STR_TO_DATE('" . $to . " 23:59:59', '%c/%e/%Y %T')";

 

so yea, it works, but don't know if that was the most efficient way to do that.  i tried playing with CAST() to format the selection as DATETIME, but it was always the $to date that caused problems.  i guess with CAST as DATETIME and there is not time specified, it defaults to 00:00:00.  this brings about a problem if the user wanted to only search one day range, entering the same date for both $from and $to.  this was my solution.  any critique or other ideas?

[fenway]

You should have these as proper DATETIME fields in the database!  You can easily add another column, issue the relevant update statements using the format string you've created above, and then you never need to do this again.  It will be much faster.

 

As for the second question, you can use DATE() to trim the time part.

[ahs10]

cool on the DATE() part, i'll do that, thanks.

 

ok i used TIMESTAMP to make use of the AUTO UPDATE feature.  i guess i don't understand the differences between TIMESTAMP and DATETIME and looking into the mysql manual doesn't make things any clearer either.  why would storing these as DATETIME compared to TIMESTAMP help?

[ahs10]

nevermind, i re-read your post.  it seems you thought that was an UPDATE statement, it's a SELECT statement.  i'm converting the input of a user to match the proper TIMESTAMP format.

[Barand]

  i'm converting the input of a user to match the proper TIMESTAMP format.

 

You should be doing that before writing it to the database table

[fenway]

  i'm converting the input of a user to match the proper TIMESTAMP format.

Which is why I suggested an UPDATE to patch everything

You should be doing that before writing it to the database table