Convert Date and Time to UTC

I have dealt with PHP dates and times in the past but it has never been this difficult and time consuming to pick back up.

This week I needed to change a timestamp from Eastern standard time to a UTC for storing in my database. Woah, did this tax my brain!

Anyways here are only four ways (I am sure there are more) I found for changing a date and time to a UTC timestamp.

1. [PHP] Using mktime()

date_default_timezone_set('America/New_York'); 
$date = mktime(0,0,0,7,16,2013); // Construct date to generate a UNIX timestamp (integer)
echo gmdate("Y-m-d H:i:s", $date); // Alternatively date('Y-m-d H:i:s',$date);

2. [PHP] Using strtotime()

date_default_timezone_set('America/New_York'); 
$date = strtotime('2013-07-16 00:00:00'); // Construct date to generate a UNIX timestamp (integer)
echo gmdate("Y-m-d H:i:s", $date); // Alternatively date('Y-m-d H:i:s',$date);

3. [MySQL] Using FROM_UNIXTIME()

This works on-the-fly when inserting your date into the database. And like the above it needs the integer representation of the UNIX timestamp.

date_default_timezone_set('America/New_York'); 
$date = strtotime('2013-07-16 00:00:00'); // Construct date to generate a UNIX timestamp (integer)
$SQL = "INSERT INTO table_name (id, created) VALUES (1,FROM_UNIXTIME({$date}))"; // MySQL does the conversion when you insert the row

4. [MySQL] Using UTC_TIMESTAMP()

This works quickest but only inserts the current date and time into the database. Simple but useful.

$SQL = "INSERT INTO table_name (id, created) VALUES (1,UTC_TIMESTAMP())"; // MySQL does the conversion when you insert the row

Realizations

While changing the integer version of an UTC timestamp to the human readable, SQL version and wasting so much time. I came to a realization, maybe one should store the UTC timestamp in integer form AS IS and convert it on the fly during presentation. Its smaller and easier to compare, who knows, maybe to avoid a little server processing by doing the presentations conversion in JavaScript. Ah well, at least this adventure helped (sort of) remind me of how time conversions works in PHP. But boy are there way too many functions for dealing with it.

A MySQL Database Assistant (Helper) Class

Connecting to a MySQL database is pretty easy but there are things you find yourself always doing over and over again on different projects like sanitizing, outputting your result arrays, changing your errors from displaying on screen to being written to the Apache error log. That’s why I have created this simple database assistant class.

$assistant = new DbAssistant('localhost','database', 'root', 'password');
$assistant->setOutputErrors(true); // display output errors on screen
$assistant->sanitize($user_input);

The following returns a basic results array:

$results = $assistant->executeQuery("SELECT * FROM mytable");

Or the following prints the results array:

$assistant->executeQueryAndDisplayResults("SELECT * FROM mytable");

View: db_helper.php.txt



Warning: This class does not currently support parameterized statements. Please use parameterized statements to avoid SQL injecton attacks.


Parameterized Statements Resource: http://php.net/manual/en/mysqli.quickstart.prepared-statements.php

Here are some usage examples:

Matching Complete Words With RegEx

This is a useful tip for when you want to match a word to another word exactly. For example say I want to match the word ‘digit’ in a MySQL query. In a normal LIKE query the word ‘digitize’ would throw a match. We know these two words are NOT the same thing and therefore should not create a match. This is how we would create a query looking for an exact match of the word ‘digit’.

SELECT * FROM `mytable` WHERE `field` REGEXP '[[:<:]]myword[[:>:]]';