conversion of datetime datatype to date and time datatype in php/mysql

Question

I am a newbie. I started php coding few days back. I want to copy a "datetime" datatype in php to fields that are of "date" and "time" datatype.

I have kept the field name datetime_info for the datetime value. It exists in try1 table. date is the name of field for "date" datatype and time is the name for "time" datatype. These two exist in try2 table.

Here is what I have written.

$result = mysql_query("SELECT * FROM try1");

while ($row = mysql_fetch_array($result))
{

    $result_update = mysql_query("INSERT INTO try2 (date, time) VALUES ('".$row_team['datetime_info']."', '".$row_team['datetime_info']."')");
    if (!$result_update)
    die('Error: ' . mysql_errno() . mysql_error());
}

The values stored in "try1" table are:

id  datetime_info
1   2008-10-02 00:00:00
2   2008-10-09 00:00:00

The expected response should be the date and time stored in respective fields. However, the output is

id  date        time
2   0000-00-00  00:00:00
3   0000-00-00  00:00:00

Can anyone explain me why and how? I tried a lot of sites but did not find any proper explanation for this. Thank you in anticipation.

Regards, BasicGem

See also MySQLi php.net/manual/en/book.mysqli.php

Mob
– Mob

2011-10-04 12:13:54 +00:00
Commented Oct 4, 2011 at 12:13 — Mob
– Mob, Commented Oct 4, 2011 at 12:13

Community · Accepted Answer · 2017-05-23 11:47:52Z

4

First of all, you shouldn't have to use PHP to do the hard work here. MySQL is very powerful to do these kinds of tasks on it's own.

Since you are copying all rows from table try1 into try2 (but splitting the datetime column), the following should work:

INSERT INTO
  `try2`
  SELECT
    null, DATE( `datetime_info` ), TIME( `datetime_info` )
  FROM
    `try1`

What this does is: for every record found in try1 insert the following values into try2:

null means use the auto_increment functionality of the id column in try2 (this is presuming the id column is indeed an auto incrementing primary key field)
DATE( `datetime_info` ) means use the DATE part of the datetime_info column of try1
TIME( `datetime_info` ) means use the TIME part of the datetime_info column of try1

Simply doing mysql_query( $sql ) where $sql is a string that represents above query should suffice then. No need to loop through any results first with PHP.

I was gonna write a PHP solution as well, but others have already done so. I would have suggested this answer.

edited May 23, 2017 at 11:47

CommunityBot

11 silver badge

answered Oct 4, 2011 at 12:16

Decent Dabbler

22.8k11 gold badges77 silver badges107 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ghazanfar Mir Over a year ago

Just wondering, what would this 'null' insert into 'try2'? Would it retain the first column as primary key value or put 'NULL'??

Decent Dabbler Over a year ago

@GhazanfarMir: I was already updating my answer with some additional explanation. Hope this explains it well enough. If not: please let me know.

Ghazanfar Mir Over a year ago

Coool - thanks for the clarification. Just spotted a wrong function name in 3rd bullet. It should be TIME :)

Ghazanfar Mir · Accepted Answer · 2011-10-04 12:11:48Z

1

$result = mysql_query("SELECT * FROM try1");

while ($row = mysql_fetch_array($result))
{
   $split = explode(' ', $row_team['datetime_info']);

   $result_update = mysql_query("INSERT INTO try2 (date, time) VALUES ('". $split[0]."', '".$split[1]."')");
    if (!$result_update)
    die('Error: ' . mysql_errno() . mysql_error());
}

answered Oct 4, 2011 at 12:11

Ghazanfar Mir

3,5412 gold badges28 silver badges42 bronze badges

1 Comment

BasicGem Over a year ago

Yes I got it. Thank you so much Mir :)

hakre · Accepted Answer · 2011-10-04 12:18:41Z

1

Because your taking it into a format and pushing it to fit another one. Just use explode() and you'll have the 2 parts you need.

list($date, $time) = explode(' ', $row_team['datetime_info']);

edited Oct 4, 2011 at 12:18

hakre

200k55 gold badges454 silver badges865 bronze badges

answered Oct 4, 2011 at 12:12

Damien Pirsy

25.5k8 gold badges72 silver badges77 bronze badges

2 Comments

BasicGem Over a year ago

Yes, that worked. Thanks a lot :) Just for clarification, we can use explode and it will automatically split the required data using ' ' as delimiter. correct?

hakre Over a year ago

@BasicGem: correct. You know where you can find the manual to read more about this function?

N.B. · Accepted Answer · 2011-10-04 12:12:31Z

0

Field of type date stores the date information, meaning year, month and day (in that order). time field, as you might have guessed, stores time information in format of hour:minute:second.

If you take a look at what you did, which is inserting a datetime into date field and time field respectively - MySQL doesn't recognize the format (you are passing the data in the wrong format that's expected for date or time field), hence it stores it as default date value.

You need to parse the output of datetime_info column into the date part and time part and then feed it to mysql.

answered Oct 4, 2011 at 12:12

N.B.

14.1k3 gold badges47 silver badges56 bronze badges

1 Comment

BasicGem Over a year ago

That is true. I used the above mentioned explode examples and it worked fine. Thanks for your help N.B. :)

Collectives™ on Stack Overflow

conversion of datetime datatype to date and time datatype in php/mysql

4 Answers 4

3 Comments

1 Comment

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

1 Comment

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related