TheLearner TheLearner - 4 months ago 38
MySQL Question

Unable to enter date time value returned by PayPal IPN into MySQL table

Paypal IPN is returning a $_POST payload which among other things, consists of a datetime item named

payment_date
. The value of this variable is in the following format:

23%3A54%3A48+Jul+23%2C+2016+PDT


This is of course, urlencoded. I am processing it for a db update as follows:

$payment_date = date( 'Y-m-d H:i:s', urldecode($_POST['payment_date']) );


This final
$payment_date
variable is what I'm later attempting to insert into my table using PDO. However, the above process is returning an error:

A non well formed numeric value encountered in /purchases/paypal_ipn.php on line 70


Despite the error, the insert is still working but the date being entered is incorrect:

1970-01-01 00:00:00


Before this, I had tried just the urldecode without any formatting:

$payment_date = urldecode($_POST['payment_date']) );


This one allowed the db insert without any error but the value entered was still incorrect:

0000-00-00 00:00:00


Is there anything I'm missing here?

Answer

Here is what the decoded timestamp from PayPal looks like:

23:54:48 Jul 23, 2016 PDT

This is not in a format which is standard to MySQL, but you can convert it using a combination of STR_TO_DATE and CONVERT_TZ:

SELECT CONVERT_TZ(STR_TO_DATE(SUBSTRING(col, 1, 21), '%H:%i:%s %b %d, %Y'),
                  SUBSTRING(col, 23),      -- convert from PDT
                  '+00:00')                -- convert to UTC time

This answer does two things. First, the call to STR_TO_DATE converts the PayPal timestamp into a legitimate MySQL datetime. If you don't care about timezone, you can stop here. If you do care about time zone, then you can also call CONVERT_TZ on the timestamp to convert it from California daylight saving time to UTC time.

Comments