• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 572
  • Last Modified:

Changing timestamps in a mysql query

So there's a table called content. In that table there's a status, article, and timestamp field. For all articles of status zero, I would like the first half to have the current time stamp, and then for every three, add another half hour. Then, after 8 hours, if there are any articles left, publish the bulk of them at 8 and a half hours. And they need to be in this format: 2006-11-28 06:56:00

So in pseudo code, it would be like this....

count = num all articles
for all articles where row < count/2, article = current time stamp.
starting at article where row = count/2, article = currenttime stamp + half hour
repeat twice
article = current time stamp + hour
repeat twice
article = current time stamp + hour and half
repeat twice
...
...
then all of the rest of the articles = current time stamp + 8 and a half hours
0
MeridianManagement
Asked:
MeridianManagement
  • 4
  • 4
1 Solution
 
Aleksandar BradarićSoftware DeveloperCommented:
What are you having problems with? The date function you can use is `date_add()`, e.g. for `= currenttime stamp + half hour` you can use:
---
...
yourFiled = date_add(now(), INTERVAL 30 MINUTE)
...
---

Same for the rest (`= current time stamp + hour`, `= current time stamp + hour and half` and `= current time stamp + 8 and a half hours`):
---
...
yourFiled = date_add(now(), INTERVAL 1 HOUR)
yourFiled = date_add(now(), INTERVAL 90 MINUTE)
yourFiled = date_add(now(), INTERVAL 510 MINUTE)
...
---

Or do you need the script code?


0
 
MeridianManagementAuthor Commented:
i guess I need the script code because I don't know how to put in the appropriate conditions to make those queries happen.
0
 
Aleksandar BradarićSoftware DeveloperCommented:
Here it goes. It's PHP and I've included two of my functions to execute and fetchs data:
---
<?php

  $my_server   = 'localhost';
  $my_username = 'your_username';
  $my_password = 'your_password';
  $my_database = 'your_database';
 
  function sql_open_and_fetch($aSQL, $aIndex = 0) {
    global $my_server;
    global $my_username;
    global $my_password;
    global $my_database;

    $result = false;

    if($aSQL != '') {
      # connect to database...
      $data = mysql_connect($my_server, $my_username, $my_password);
      if(!empty($data)) {
        if(mysql_select_db($my_database)) {
          mysql_query('SET NAMES utf8');
          $query = mysql_query($aSQL);
          if($query && $row = mysql_fetch_array($query)) {
            $result = $row[$aIndex];
          }
        }
        mysql_close($data);
      }
    }

    return $result;
  }

  function sql_exec($aSQL, $return_affected = 0, $return_insert_id = 1) {
    global $my_server;
    global $my_username;
    global $my_password;
    global $my_database;

    $aID = false;
    if($aSQL != '') {
      # connect to database...
      $data = mysql_connect($my_server, $my_username, $my_password);
      if(!empty($data)) {
        if(mysql_select_db($my_database)) {
          mysql_query('SET NAMES utf8');
          $theSQL = $aSQL;
          $query = mysql_query($theSQL);
          if($query) {
            if($return_affected)
              $aID = mysql_affected_rows();
            else if($return_insert_id)
              $aID = mysql_insert_id();
            else
              $aID = true;
          }
        }
        mysql_close($data);
      }
    }

    return $aID;
  }
 
  # Get total number of rows first...
  $aSQL = 'SELECT count(*) FROM content WHERE status = 0 ';
  $numRows = sql_open_and_fetch($aSQL);
 
  $start = 0;
  $count = $numRows / 2;
  $offset = 0;
  while($start < $numRows) {
    $aSQL = "UPDATE content SET status = 1, stamp = date_add(now(), INTERVAL $offset MINUTE) WHERE status = 0 ORDER BY id LIMIT $count ";
    sql_exec($aSQL);
   
    $start += $count;
    $count = 3;
    $offset += 30;
   
  }

?>
---
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
Aleksandar BradarićSoftware DeveloperCommented:
Sorry, I've overlooked the 8.5 hour condition. Replace the main section with this:
---
  while($start < $numRows) {
    if(offset >= 510) {
      $aSQL = "UPDATE content SET status = 1, stamp = date_add(now(), INTERVAL $offset MINUTE) WHERE status = 0 ";
    }
    else {
      $aSQL = "UPDATE content SET status = 1, stamp = date_add(now(), INTERVAL $offset MINUTE) WHERE status = 0 ORDER BY id LIMIT $count ";
    }
    sql_exec($aSQL);
   
    $start += $count;
    $count = 3;
    $offset += 30;
  }
---

It removed the LIMIT when it reaches the 8.5 hours limit. Hope it helps :)
0
 
Aleksandar BradarićSoftware DeveloperCommented:
Another modification:
---
  while($start < $numRows) {
    if(offset >= 510) {
      $aSQL = "UPDATE content SET status = 1, stamp = date_add(now(), INTERVAL $offset MINUTE) WHERE status = 0 ";
      $start = $count;
    }
    else {
      $aSQL = "UPDATE content SET status = 1, stamp = date_add(now(), INTERVAL $offset MINUTE) WHERE status = 0 ORDER BY id LIMIT $count ";
    }
    sql_exec($aSQL);
   
    $start += $count;
    $count = 3;
    $offset += 30;
  }
---

This allows it to finish the loop. Should be fine now.
0
 
MeridianManagementAuthor Commented:
I will try it out and let you know. Thanks.
0
 
MeridianManagementAuthor Commented:
I think the concept is close... however... I ran it on a group of about 500 articles and it only replaced around... 25%
0
 
MeridianManagementAuthor Commented:
Your logic was a little off but I fixed it. Thanks for the code though it worked out great.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now