I have a website which have a date and time combined in the start of meeting and end of meeting.

Meeting_start 2019-04-26 11:39:15

I already created new columns for date and time. How can I split the existing date and time then transfer them to their new respective columns?

Ps. I'm looking for SQL codes.

3 Answers

1
RamRaider On

You can simply use the date and time functions available in mySQL

set @d='2019-04-26 11:39:15';
select date(@d),time(@d);

Will output

+------------+----------+
| date(@d)   | time(@d) |
+------------+----------+
| 2019-04-26 | 11:39:15 |
+------------+----------+

Assuming that the existing column is called meeting_start and a table called TABLE you could then do

update `TABLE` set `date_col`=date( `meeting_start` ), `time_col`=time( `meeting_start` );

A simple table schema to illustrate how to update the new fields

mysql> describe `so_datetime_fudge`;
+--------------------+------------------+------+-----+---------+----------------+
| Field              | Type             | Null | Key | Default | Extra          |
+--------------------+------------------+------+-----+---------+----------------+
| id                 | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name               | varchar(50)      | YES  |     | NULL    |                |
| meeting_start      | datetime         | YES  |     | NULL    |                |
| meeting_start_date | date             | YES  |     | NULL    |                |
| meeting_start_time | time             | YES  |     | NULL    |                |
+--------------------+------------------+------+-----+---------+----------------+

A snapshot of the table with 3 rows of dummy data

mysql> select * from so_datetime_fudge;
+----+-----------------------+---------------------+--------------------+--------------------+
| id | name                  | meeting_start       | meeting_start_date | meeting_start_time |
+----+-----------------------+---------------------+--------------------+--------------------+
|  1 | The First of Too Many | 2019-05-27 16:48:30 | NULL               | NULL               |
|  2 | Holy Cow Batman       | 2019-05-27 16:54:56 | NULL               | NULL               |
|  3 | The End is Nigh       | 2019-05-27 16:55:22 | NULL               | NULL               |
+----+-----------------------+---------------------+--------------------+--------------------+

Then running this query:

update `so_datetime_fudge` set `meeting_start_date`=date( `meeting_start` ), `meeting_start_time`=time( `meeting_start` );

Running the same SELECT query after the update

mysql> select * from so_datetime_fudge;
+----+-----------------------+---------------------+--------------------+--------------------+
| id | name                  | meeting_start       | meeting_start_date | meeting_start_time |
+----+-----------------------+---------------------+--------------------+--------------------+
|  1 | The First of Too Many | 2019-05-27 16:48:30 | 2019-05-27         | 16:48:30           |
|  2 | Holy Cow Batman       | 2019-05-27 16:54:56 | 2019-05-27         | 16:54:56           |
|  3 | The End is Nigh       | 2019-05-27 16:55:22 | 2019-05-27         | 16:55:22           |
+----+-----------------------+---------------------+--------------------+--------------------+

But, if you create a VIEW of the table you can structure this how you like and the underlying data remains unchanged

CREATE VIEW `vw_datetime_fudge` AS select 
    `id`, 
    `name`, 
    date( `meeting_start` ) as `meeting_start_date`, 
    time( `meeting_start` ) as `meeting_start_time` 
from `so_datetime_fudge`;


mysql> select * from `vw_datetime_fudge`;
+----+-----------------------+--------------------+--------------------+
| id | name                  | meeting_start_date | meeting_start_time |
+----+-----------------------+--------------------+--------------------+
|  1 | The First of Too Many | 2019-05-27         | 16:48:30           |
|  2 | Holy Cow Batman       | 2019-05-27         | 16:54:56           |
|  3 | The End is Nigh       | 2019-05-27         | 16:55:22           |
+----+-----------------------+--------------------+--------------------+
0
Jimmix On
<?php

$subject = 'Meeting_start 2019-04-26 11:39:15';

$pattern = '/(\d\d\d\d\-\d\d\-\d\d).*(\d\d\:\d\d\:\d\d)/U';


preg_match_all($pattern,$subject,$matches);

$date = $matches[1][0];
$time = $matches[2][0];

echo $date . '<br>' . PHP_EOL;
echo $time . '<br>' . PHP_EOL;

gives result

2019-04-26<br>
11:39:15<br>
0
Vitaliy Krushelnitsky On

You can use DateTime::format method

$date = new DateTime('2019-04-26 11:39:15');
$timeString = $date->format('H:i:s');
$dateString = $date->format('Y-m-d');