We help IT Professionals succeed at work.

how can I insert  php gmdate("Y-m-d\TH:i:s\Z") format into mysql database as date?

BR
BR asked
on
1,673 Views
Last Modified: 2017-04-26
Dear Experts,
I need to insert $postdate= gmdate("Y-m-d\TH:i:s\Z") variable into my database, however I can not insert it as datetime.
what do you suggest I should do?
the variable like this 2017-04-25 09:28:59Z
Comment
Watch Question

CERTIFIED EXPERT

Commented:
If you remove the Z and the tab character, it should work. Specifically,

$postdate= gmdate("Y-m-d H:i:s")

will work, but if you can't edit this,

INSERT ..... REPLACE(REPLACE('2017-04-25 09:28:59Z ','Z',''), '\t', '')
Most Valuable Expert 2011
Author of the Year 2014

Commented:
Bookmark these two articles and read them when you have questions about how to work with date and time values.

Procedural
https://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL-Procedural-Version.html

Object-oriented
https://www.experts-exchange.com/articles/20920/Handling-Time-and-Date-in-PHP-and-MySQL-OOP-Version.html

You want to format the value like this:
$postdate= gmdate('c');

Open in new window

BRDigital Marketing

Author

Commented:
Dear Ray,
whatever I do, mysql database doesn't allow me to insert the date as datetime.

As you said I used $postdate= gmdate('c'); to insert the date, however it gives me below error. I had read your article but I will read again. I think I need to learn the logic.

MySQL said: Documentation

#1292 - Incorrect datetime value: '2017-04-26T11:37:30+00:00' for column 'RESPONSE_DATETIME' at row 1

should I convert the date before  I insert it? Let me read the article first. Then I will come back here... thank you
Most Valuable Expert 2011
Author of the Year 2014

Commented:
Something else must be amiss, because I use this kind of code all the time.  Make sure your MySQL timezone is UTC if you're going to use UTC timezone values in PHP.  Or at least make sure the timezone settings match.  What can go wrong if they don't match?  Daylight Savings Time is "missing" some time when the clocks are set forward, and has some duplicate times when the clocks are set back.  If your app generates a date/time value that is in these "gray zones" you may find that PHP and MySQL do not agree about the time!

Here's an example that shows how I define and use the columns.
https://iconoun.com/demo/mysqli_datetime_example.php
<?php // demo/mysqli_datetime_example.php
/**
 * Demonstrate some of the basics of MySQLi with DateTime Columns
 *
 * References for PHP and MySQL(i):
 *
 * http://php.net/manual/en/mysqli.overview.php
 * http://php.net/manual/en/class.mysqli.php
 * http://php.net/manual/en/class.mysqli-stmt.php
 * http://php.net/manual/en/class.mysqli-result.php
 * http://php.net/manual/en/class.mysqli-warning.php
 * http://php.net/manual/en/class.mysqli-sql-exception.php <-- DID NOT WORK PHP 5.3+, MySQL 5.1+
 *
 * http://php.net/manual/en/mysqli.construct.php
 * http://php.net/manual/en/mysqli.real-escape-string.php
 * http://php.net/manual/en/mysqli.query.php
 * http://php.net/manual/en/mysqli.errno.php
 * http://php.net/manual/en/mysqli.error.php
 * http://php.net/manual/en/mysqli.insert-id.php
 *
 * http://php.net/manual/en/mysqli-result.num-rows.php
 * http://php.net/manual/en/mysqli-result.fetch-array.php <-- DO NOT USE THIS
 * http://php.net/manual/en/mysqli-result.fetch-object.php
 */
ini_set('display_errors', TRUE);
error_reporting(E_ALL);
echo '<pre>';


// DATABASE CONNECTION AND SELECTION VARIABLES - GET THESE FROM YOUR HOSTING COMPANY
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";
$db_user = "??";
$db_word = "??";

// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
$mysqli = new mysqli($db_host, $db_user, $db_word, $db_name);

// DID THE CONNECT/SELECT WORK OR FAIL?
if ($mysqli->connect_errno)
{
    $err
    = "CONNECT FAIL: "
    . $mysqli->connect_errno
    . ' '
    . $mysqli->connect_error
    ;
    trigger_error($err, E_USER_ERROR);
}

// ACTIVATE THIS TO SHOW WHAT THE DB CONNECTION OBJECT LOOKS LIKE
// var_dump($mysqli);


// CREATING A TABLE FOR OUR TEST DATA
$sql
=
"
CREATE TEMPORARY TABLE my_table
( id    INT         NOT NULL AUTO_INCREMENT PRIMARY KEY
, xdate DATETIME    NOT NULL DEFAULT '0000-00-00 00:00:00'
, xwhen TIMESTAMP   NOT NULL
, thing VARCHAR(4)  NOT NULL DEFAULT ''
)
"
;

// IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res = $mysqli->query($sql))
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}

// ACTIVATE THIS TO SHOW THE RESULTS OF THE QUERY
// var_dump($res);


// CONSTRUCT THE INSERT QUERY
$xdate = date('Y-m-d H:i:s');
$sql = "INSERT INTO my_table ( xdate, thing ) VALUES ( '$xdate', 'One' )";

// RUN THE QUERY TO INSERT THE ROW
$res = $mysqli->query($sql);

// IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}

// GET THE AUTO_INCREMENT KEY OF THE RECORD JUST INSERTED
$id  = $mysqli->insert_id;
echo "MySQLI INSERTED A ROW CONTAINING <b>$xdate</b> WITH AUTO_INCREMENT ID = $id" . PHP_EOL;
echo PHP_EOL;


// SELECT THE ROW
$sql = "SELECT * FROM my_table";
$res = $mysqli->query($sql);
$row = $res->fetch_object();
print_r($row);


// SLEEP A BIT
echo PHP_EOL . "SLEEPING..." . PHP_EOL;
sleep(7);


// CONSTRUCT THE UPDATE QUERY
$xdate = date('Y-m-d H:i:s');
$sql = "UPDATE my_table SET thing = 'Two' WHERE id='$id' LIMIT 1";
$res = $mysqli->query($sql);
if (!$res)
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}


// SELECT THE NEW VALUE
$sql = "SELECT * FROM my_table";
$res = $mysqli->query($sql);
$row = $res->fetch_object();
print_r($row);

Open in new window

CERTIFIED EXPERT
Expert of the Year 2008
Top Expert 2008

Commented:
>> I need to insert $postdate= gmdate("Y-m-d\TH:i:s\Z") variable into my database
The above should end  up setting $postdate to something like 2017-04-25T09:28:59Z.  However, to insert that into mysql, it expects you to drop the "T" and the "Z", which would leave you with the following format: 2017-04-25 09:28:59.
If you have a lot of dates with the "T" and the "Z" and are just trying to simplify the INSERT process, all you need is:

$postdate= gmdate("Y-m-d\TH:i:s\Z");
$sql="INSERT INTO someTable (someDateTimeField) VALUES( STR_TO_DATE($postdate, '%Y-%m-%dT%H:%i:%sZ') )";
// then execute your insert statement

Open in new window


If you then examine the data in the db you will see "yyyy-mm-dd hh:nn:ss" (No "T" and no "Z" because mysql doesn't store that for Date related fields).  Now, upon retrieval, if you want the "T" and the "Z" back, then you just use the DATE_FORMAT() function:
SELECT DATE_FORMAT(someDateTimeField, '%Y-%m-%dT%H:%i:%sZ') FROM someTable

Open in new window

Most Valuable Expert 2011
Author of the Year 2014
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.