ahs10 Posted February 19, 2008 Share Posted February 19, 2008 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? Link to comment https://forums.phpfreaks.com/topic/91951-is-this-the-most-efficient-way-to-do-this/ Share on other sites More sharing options...
fenway Posted February 19, 2008 Share Posted February 19, 2008 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. Link to comment https://forums.phpfreaks.com/topic/91951-is-this-the-most-efficient-way-to-do-this/#findComment-470931 Share on other sites More sharing options...
ahs10 Posted February 19, 2008 Author Share Posted February 19, 2008 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? Link to comment https://forums.phpfreaks.com/topic/91951-is-this-the-most-efficient-way-to-do-this/#findComment-470947 Share on other sites More sharing options...
ahs10 Posted February 19, 2008 Author Share Posted February 19, 2008 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. Link to comment https://forums.phpfreaks.com/topic/91951-is-this-the-most-efficient-way-to-do-this/#findComment-470952 Share on other sites More sharing options...
Barand Posted February 20, 2008 Share Posted February 20, 2008 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 Link to comment https://forums.phpfreaks.com/topic/91951-is-this-the-most-efficient-way-to-do-this/#findComment-472107 Share on other sites More sharing options...
fenway Posted February 20, 2008 Share Posted February 20, 2008 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 Link to comment https://forums.phpfreaks.com/topic/91951-is-this-the-most-efficient-way-to-do-this/#findComment-472136 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.