rearrange dates in a DB table

[ajoo]

Hi all, 

I have a set of dates as below:

Quote

TimeOfLogin                      TimeOfLogout
2015-08-10 22:31:58        2015-08-10 22:39:43
2015-08-14 16:03:31        2015-08-14 16:12:13
2015-09-05 13:12:10        2015-09-06 07:27:38
2015-09-07 07:36:11        2015-09-07 07:42:09
2015-09-08 17:43:03        2015-09-08 17:49:28
2017-09-11 19:03:29        2017-09-11 19:29:54
2017-09-22 21:42:39        2017-09-22 21:51:14
2015-11-15 09:13:22        2015-11-20 17:03:51
2015-11-23 13:25:01        2015-11-23 13:42:01
2016-01-03 21:41:44        2016-01-03 21:49:02
2016-03-05 15:30:37        2016-03-05 15:51:57
2016-04-14 20:22:47        2016-04-14 20:25:46
2016-10-16 22:53:46        2016-10-17 00:47:15
2017-08-31 13:34:33        2017-08-31 14:01:59
2018-04-28 15:06:47        2018-04-28 15:10:15

I wish to rearrange / UPDATE these dates in the DB to have a uniform gap between them like that of 1 or 2 or 3 or 0 or n days and a uniform interval of time ( 2 or 3 or 4 or 5 or n hours) between TimeOfLogin and TomeOfLogout.

What would be the best way to achieve that? Should I i use php for this or is there an all mysql way to achieve this !!

Thanks all !

 

[Phi11W]

14 minutes ago, ajoo said:

... a uniform gap between them like that of 1 or 2 or 3 or 0 or n days and a uniform interval of time ( 2 or 3 or 4 or 5 or n hours) ...

It's unclear (to me) what you want. 

To me, "uniform" generally means "the same", not "... 1 or 2 or 3 or 0 ...". 

If you want a pre-defined set of data, then delete the rows and reinsert them the way you want them. 

Also; you say "gap".  Remember that rows in a relational table have no intrinsic order. 

Regards, 
   Phill  W. 

[Barand]

I assume you want to generate new data and not change the actual times that you already have.

I would use a mix

• php to generate the gaps between logins
• sql to generate the duration of the login

EG

$login_interval = 3;  // days between logins
$login_duration = 5;  // logged duration

$startdate = '2018-08-01 09:00:00';

$dt = new DateTime($startdate);
$di = new DateInterval("P{$login_interval}D");
$dp = new DatePeriod($dt, $di, 19);                  // generate 20 dates

$stmt = $db->prepare("INSERT INTO ajoo (TimeOfLogin, TimeOfLogout) VALUES (?, ? + INTERVAL ? HOUR)");

foreach ($dp as $d) {
    $login = $d->format('Y-m-d H:i:s');
    $stmt->execute( [$login,
                     $login,
                     $login_duration
                     ]);
}

 

[ajoo]

Hi Phi11W, 

 

Thanks for the reply. 

What I mean is that I want a uniform gap of 'n' days between two rows ( where n can be any one of 1,2,3 .. n), say 2 days. I want to automate this using either php or mysql. Further the time difference between TimeOfLogin and TimeOfLogout should also be uniform ( say 1hour ). I hope that makes the problem more clear.

Thanks.

 

 

[ajoo]

Hi Guru Barand !

Thanks loads for the reply.  I want to actually update the existing records in my DB. SO its more like an update. But I think I should be able to take your reply and modify it for my purpose. 

Thanks a  ton !

 

 

 

[Barand]

Easier to delete them and add new ones as Phi11W suggested

[Psycho]

I'm not sure what the OP is really wanting here as the "requirements" are very confusing. Right now, the DB contains a timestamp for a login and a timestamp for a logout. If the intent is to replace those values with a human readable period of time (i.e. 5 hours, 23 minutes) then no changes to the DB should be made. That type of logic should be made in the output process. There are plenty of resources that will take two timestamps and produce a period of time output.

[taquitosensei]

If that is what the OP is after. There's a built in date class in php that will do it for you. From the documentation. 

$d1=new DateTime("2012-07-08 11:14:15.638276"); 
$d2=new DateTime("2012-07-08 11:14:15.889342"); 
$diff=$d2->diff($d1); 
print_r( $diff ) ; 

/* returns: 

DateInterval Object 
( 
    [y] => 0 
    [m] => 0 
    [d] => 0 
    [h] => 0 
    [i] => 0 
    [s] => 0 
    [invert] => 0 
    [days] => 0 
) 

*/