Best practice for storing the date in MySQL from PHP
I’ve been using the unix timestamp all my life. I like it because it’s easy to compare, it’s fast because I store it as an integer. And since I’m using PHP, I can get any date/time format with date() function from the unixtimestamp. Now, some people are saying that it’s best to use the DATETIME format. But besides the more suited name, I don’t see any advantages. Is it indeed better to use DATETIME, if so, what are the advantages? Thanks.
7 Answers 7
If you store dates as Unix timestamps in the database, you’re giving yourself the heavy lifting. You have to convert them to the formats you want to use, you have to do the calculations between date ranges, you have to build the queries to get data in a range. This seems counter-intuitive- surely your «programmer time» is best spent solving real problems?
It seems much better practice to store dates and times in the proper format that MySQL has available, then use the database functions to create the queries for the data you want. The time you would waste doing all the convertions and mucking about is massive compared to the afternoon spent reading (and understanding) 11.6 MySQL Date and Time Functions
MySQL’s DATETIME field lacks a timezone — always store dates in UTC (i.e. avoid NOW() — use UTC_TIMESTAMP()) otherwise you’re going to get into a big mess as you try to internationalize your app and for whatever reason change your servers timezone.
I’ve also been a huge fan of the unix timestamp all my life. But I think the correct answer is: «depends». I recently did a single table database where I wanted to only list URLs. There would be a date field, but the date field is purely for sorting. I.e order by last_crawled. Which means I will never use any built-in date functions on that field. It is merely an easy way to get the oldest entries first and I will never apply date functions to this field. Now, had I made this a date field, I would have lost out on two things:
- A datetime field is twice the size of an integer
- Sorting by an integer is faster (not 100% sure of this, pending outcome of this question)
However, for another system I had to store transactional information. This made using internal mysql date functions possible which turned out to be very useful when we had to start doing reports.
One advantage of using the MySQL date/time types is to be able to more simply use the date/time functions in MySQL.
The DATE type also has the advantage in that its only storing day, month and year so there is no space wasted or comparison complication that a seconds since epoch time would have for situations where you only cared about the day and not the time.
Personally I tend to use a database as just a dump for data so such functions are of little interest. In PHP I tend to just store the date in integer format for pretty much the reasons you state.
@Smita V, the inefficient query to which you refer is only so because you’re applying your conversion function incorrectly to every table row, where you should apply it to the condition itself. So instead of
select col1,col2,colUnixdatetime from table where From_Unixtime(colUnixdatetime) between wtvdate1 and wtvdate2 , which converts every row on the table to compare it to the date you’ve got. You should use
select col1,col2,colUnixdatetime from table where colUnixdatetime between UNIX_TIMESTAMP(wtvdate1) and UNIX_TIMESTAMP(wtvdate2). Doing it this way WILL use the appropriate table indexes.
@treznik a while ago I moved from a uts integer to a datetime or timestamp data types, for the reasons mentioned above, in that they’re much easier to read and manipulate (I do quite a lot of direct table access). However I’ve lately started to re-think this approach for two reasons:
- There is no time zone location stored, so you’re inferring the time zone based on your location. This may or may not be an issue for you.
- It ignores daylight saving time. So when the clocks go back at 2am, you will get 1:30am twice, and saying 2011-10-30 01:30 doesn’t let you know this, whereas 1319938200 does. I don’t think there’s a native way in mysql to store date including time zone, except as a string (2011-10-30 01:30 BST).
I’m still trying to figure out the answer to this, myself.
convert php date to mysql format
How do I convert this to MySql format 0000-00-00 for inclusion in db. Is it along the lines of: date(‘Y-m-d’ strtotime($date);. The reason I ask, is because I have tried variations of this and I cannot seem to get it to work. Either displays as 1970 or some other variation of that. Many thanks
9 Answers 9
$date = mysql_real_escape_string($_POST['intake_date']); 1. If your MySQL column is DATE type:
$date = date('Y-m-d', strtotime(str_replace('-', '/', $date))); 2. If your MySQL column is DATETIME type:
$date = date('Y-m-d H:i:s', strtotime(str_replace('-', '/', $date))); You haven’t got to work strototime() , because it will not work with dash — separators, it will try to do a subtraction.
Update, the way your date is formatted you can’t use strtotime() , use this code instead:
$date = '02/07/2009 00:07:00'; $date = preg_replace('#(\d)/(\d)/(\d)\s(.*)#', '$3-$2-$1 $4', $date); echo $date; @bollo: Have you tried my code, yet? Give me a sample datetime to test, but I suspect it will work just fine.
it is still reversing the d & m. for example, using your code, after conversion, this is what appears in db: 2009-02-07 00:07:00 but it should be 2009-07-02 00:07:00. The response in firbug is showing the post as 02/07/2009 00:07:00. THANKS
@bollo: The way your date is formatted, it is not possible to use strtotime() . I updated my answer. Please check the code on the update part.
I am not up much on preg_replace. What exactly is code doing? also, how is my date formatted differently? I am using this format on other pages with no probs. As a test, i sent the response back through firebug json and this is the result: «date»: «2009-02-07 00:07:00», so it isn,t reaching the db correctly. Thanks
@bollo: The way you have your date right now, it is DD/MM/YYYY , it should be MM/DD/YYYY to work with strtotime() . The preg_replace() above will format the date to the output style I am showing above. Maybe you sent back the wrong variable, because I just double checked it, and it is responding with the way you want the date formatted.
This site has two pretty simple solutions — just check the code, I provided the descriptions in case you wanted them — saves you some clicks.
1.One common solution is to store the dates in DATETIME fields and use PHPs date() and strtotime() functions to convert between PHP timestamps and MySQL DATETIMEs. The methods would be used as follows —
$mysqldate = date( 'Y-m-d H:i:s', $phpdate ); $phpdate = strtotime( $mysqldate ); 2.Our second option is to let MySQL do the work. MySQL has functions we can use to convert the data at the point where we access the database. UNIX_TIMESTAMP will convert from DATETIME to PHP timestamp and FROM_UNIXTIME will convert from PHP timestamp to DATETIME. The methods are used within the SQL query. So we insert and update dates using queries like this —
$query = "UPDATE table SET datetimefield = FROM_UNIXTIME($phpdate) WHERE. "; $query = "SELECT UNIX_TIMESTAMP(datetimefield) FROM table WHERE. "; Getting a PHP DateTime object into a string representation of a MYSQL date
I have a DateTime which I want to store in a Date MySQL column. I am using MySQLi and prepared statements. When binding parameters, I cannot specify date as a type, only strings and integers. How do I convert the DateTime to a MySQL date string representation? There is very little documentation on the DateTime class.
Almost closed this as a duplicate question, then I realized it was different and I reopened it. Please excuse my haste!
2 Answers 2
I almost closed this as a duplicate of Convert from MySQL datetime to another format with PHP but I think it’s actually the reverse problem. That question asked how to format a date fetched from MySQL, and you’re asking how to format a date for input to a query.
SELECT * FROM MyTable WHERE DateTimeCol = ? You can format the value to the YYYY-MM-DD HH:MM format MySQL wants:
Then submit it as a string parameter to the query.
SELECT * FROM MyTable WHERE DateTimeCol = FROM_UNIXTIME(?) Then you can submit the timestamp value as a numeric parameter to the query.
SELECT * FROM MyTable WHERE DateTimeCol = STR_TO_DATE(?, '%m/%d/%y') You can submit a wide variety of string representations of date/time, if you specify the formatting to MySQL’s STR_TO_DATE() function. For the formatting codes, see the DATE_FORMAT() function.
Second are optional, but yes that works. If you use ‘H’ (12-hour clock) instead of ‘G’ (24-hours clock), then you should also include AM/PM. Refer to DATE_FORMAT() documentation for full list of codes.
‘h’ is 12-hour clock, ‘H’ is 24 with leading zeros and ‘G’ is 24 without leading zeros. You should indeed use ‘H’.
I’ve edited the format string in option 1 above with respect to these comments from ʞɔıu and christian studer.
Because the DateTime-class introduced with PHP5 is poorly documented, i wouldn’t recommend to use it. I find regular timestamps to be much easier to work with!
But if you still want to use DateTime-objects, a solution would be to transform the objects into (unix) timestamps before storing them in your database. Transform the timestamps back to DateTime-objects after reading information from your database.
To create DateTime-object from a timestamp, use date_create() with the timestamp as argument. To get your objects representation of number of seconds since the Unix Epoch, use date_format($object, ‘U’) (uppercase U).
MySQL datetime into PHP
I have found a proper solution to my «problem» but even after reading mysql pages, I don’t understand the logic behind it. I currently store registration information in my system in a «datetime» formatted field in one of my tables (YYYY-MM-DD hh:mm:ss) . When I want to display the data on one of my php pages, simply posting the exact field data shows the format mentioned above. I would THINK simply using date(«Y-m-d»,$row[«DATE»]) where $row[«DATE»] corresponds to the particular row value would return the desired format. Instead I have to use: date(«Y-m-d», strtotime($row[«DATE»])) . Why is this? My $row[«DATE»] field is not a string in the first place. Should I be able to simple rearrange the data stored in a datetime field? Wasn’t that the purpose of rebuilding my entire tableset to accomodate datetime?
2 Answers 2
MySQL has a built in function called date_format which you can use to display the date how you want to.
SELECT DATE_FORMAT(date_field, '%Y-%m-%d') as date_field FROM table_name The manual has the list of formats and the variables needed to display it that way. Using this method there will be no need to have PHP convert it etc. Plus it is less code on PHP side for something MySQL can handle easily.
Sorry, just read you were looking for an explanation.
PHP’s date function takes in a UNIX timestamp, which MySQL is not using. MySQL uses a real date format IE: YYYY-MM-DD HH:MM:SS , as you know, this is to be compliant for years later. The UNIX timestamp has a limited range from something like 1969 to 2037 that it is valid for, which makes it really useful for «timestamping» of items such as a chat box message or items they are not expected to be around post those dates, where as the MySQL DATETIME should not die out until the year changes to 5 digits or the world ends.
Read the WIKI on UNIX timestamp for more information on it.
Parse date to store in mysql DATETIME from php [duplicate]
I’ve got a date in DD.MM.YYYY h:mm a format, how could i parse it in php or laravel to store it in a datetime field in mysql database? Here’s the date i want to parse: 29.08.2015 11:00 pm I tried to parse to parse it like this: date_create_from_format(‘DD.MM.YYYY h:mm a’,’29.08.2015 11:00 pm’); But it didn’t work, it returns false.
probably because you should investigate yourself before posting a question, and if you had done so, you would have found a thousand links that would help you already. if you then has a specific problem with your code, you could come back here (but also then, search for an answer to that problem first)
Always, If you find any answer solved your question, mark it as correct answer. It will be helpful for those who search for the same.
2 Answers 2
$date = date_create_from_format('j.m.Y h:i a','29.08.2015 11:00 pm'); echo $date->format('Y-m-j H:i:s'); new DateTime(string date) converts a string to a date object without caring the format. You can pass ’29-8-2015′,’29 Aug 2015′.. etc as $datestring. No need to define the format of input.
$datestring='29.08.2015 11:00 pm'; //defined date as string $datetime = new DateTime($datestring); //create datetime of defined date $datetime = $datetime->format('Y-m-d H:i:s'); //reformat to the format we need This question is in a collective: a subcommunity defined by tags with relevant content and experts.