I have thousands of dates in the following format:
2011-10-02T23:25:42Z
(aka ISO 8601 in UTC)
What MySQL data type should I use for storing such a ISO8601 date in a MySQL database? E.g. Datetime
, timestamp
or something else?
Which is best for comparison (eg. getting records between two dates/times) and ordering the results from queries? What about if the database is very large?
And what would be the best way to convert the above PHP string for MySQL storage? (I'm guessing date_default_timezone_set('UTC');
would be used?)
I think that keeping your date-time values in field of type DATETIME
would be kind of natural way.
From my own experience with my current PHP application, only read
/ write
operations concerning this information may be problematic.
One of possible solutions (assuming that you use DATETIME
data type) for properly performing the whole process could be the following approach:
DATETIME
fields from your database converting them in the query to string representation in the form of '2011-10-02T23:25:42Z'
by using DATE_FORMAT
MySQL function with '%Y-%m-%dT%H:%i:%sZ'
formatting string (docs on DATE_FORMAT)DateTime
class objects and DateTime::createFromFormat
static method given 'Y-m-d\TH:i:s\Z'
formatting string (T
and Z
are escaped to avoid treating them as formatting directives) (docs for the method).DateTime
class object to our ISO 8601 in UTC format string representation using DateTime
class object's format
method with the same as before 'Y-m-d\TH:i:s\Z'
formatting string (documentation).INSERT
/ UPDATE
operation on database information using such prepared string as a parameter for MySQL function STR_TO_DATE
(with '%Y-%m-%dT%H:%i:%sZ'
formatting string) which converts it to real database DATETIME
value (docs on STR_TO_DATE).Below please find a draft example of such approach using PDO objects:
$db = new PDO('mysql:host=localhost;dbname=my_db;charset=utf8', 'username', 'password');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
try {
// run the query aquring 1 example row with DATETIME data
// converted with MySQL DATE_FORMAT function to its string representation
// in the chosen format (in our case: ISO 8601 / UTC)
$stmt = $db->query("SELECT DATE_FORMAT(dt_column, '%Y-%m-%dT%H:%i:%sZ') AS formatted_dt_col"
." FROM your_table LIMIT 1");
if($stmt !== FALSE) {
$row = $stmt->fetch(PDO::FETCH_ASSOC);
// convert the acquired string representation from DB
// (i.e. '2011-10-02T23:25:42Z' )
// to PHP DateTime object which has all the logic of date-time manipulation:
$dateTimeObject = DateTime::createFromFormat('Y-m-d\TH:i:s\Z', $row['formatted_dt_col']);
// the following should print i.e. 2011-10-02T23:25:42Z
echo $dateTimeObject->format('Y-m-d\TH:i:s\Z');
// now let's write PHP DateTime class object '$dateTimeObject'
// back to the database
$stmtInsertDT = $db->prepare("INSERT INTO your_table(dt_column) "
. " VALUES ( STR_TO_DATE(:par_formatted_dt_column, '%Y-%m-%dT%H:%i:%sZ') )");
$dtAsTextForInsert = $dateTimeObject->format('Y-m-d\TH:i:s\Z');
// convert '$dateTimeObject' to its ISO 8601 / UTC text represantation
// in order to be able to put in in the query using PDO text parameter
$stmtInsertDT->bindParam(':par_formatted_dt_column', $dtAsTextForInsert, PDO::PARAM_STR);
$stmtInsertDT->execute();
// So the real insert query being perform would be i.e.:
/*
INSERT INTO your_table(dt_column)
VALUES ( STR_TO_DATE('2011-10-02T23:25:42Z', '%Y-%m-%dT%H:%i:%sZ') )
*/
}
}
catch(\PDOException $pexc) {
// serve PDOException
}
catch(\Exception $exc) {
// in case of no-PDOException, serve general exception
}
This approach helped me a lot in operating date-time values between PHP and MySQL database.
I hope it might occur helpful for you also.