Jump to content

rearrange dates in a DB table


ajoo

Recommended Posts

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 !

 

Link to comment
Share on other sites

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. 

Link to comment
Share on other sites

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
                     ]);
}

 

Link to comment
Share on other sites

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.

 

 

Link to comment
Share on other sites

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 !

 

 

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 
) 

*/ 

 

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.