Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 8 of 8
  1. #1
    Regular Coder
    Join Date
    Jan 2010
    Posts
    161
    Thanks
    10
    Thanked 1 Time in 1 Post

    Current Date -3 Days from Varchar datetime

    Hi

    I have a table that contains a varchar column so todays date is shown as 20100806121550 along with order information.

    This information is going to create an xml file at 3pm every day Monday-Fridays but if orders are placed after 3pm it would need to include these on the next days XML file. Also if orders are placed after 3PM Friday they will not be in the XML file until Monday runs.

    Otherwise is it possible to run the job as 2.50pm and mark the orders as sent if they are included in the XML and how would you achieve this.

    How in PHP can this be achieved.

    Many thanks
    Roy
    Last edited by ROYW1000; 08-06-2010 at 02:34 PM. Reason: more info

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    I think this can be done in SQL as well, but from the looks of that datetime format you are using you should be able to run that through an strtotime
    PHP Code:
    $time strtotime('20100806121550'); 
    I believe that's a valid format. Using this, you can once again manipulate it with adding and subtracting days using strtotime again:
    PHP Code:
    $less3Days strtotime('-3 day'$time); 
    As with your information, you need to flag it in someway to determine that it has been dealt with. This is why I don't think you need to manipulate your dates at all, I'm thinking you can select from your table where it has not been flagged as processed, and then simply update those values and set them to have been processed. Come monday, anything put through will be included on the monday run since it has not yet been processed. Does that make sense (or work for your situation)?
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

  • #3
    Regular Coder
    Join Date
    Jan 2010
    Posts
    161
    Thanks
    10
    Thanked 1 Time in 1 Post
    Hi

    I will test this date method if need be.

    What would be the best way to record them as processed and what sort of code would I use, the cron job will run daily at 2.50 ready for the data collection at 3pm.

    Oviously it customers orders so how would I go about marking them off as being in the feed would this happen with some sort of php code or would I be better re passing the xml file we created and marking the orders as being in the feed.

    If it passing the XML feed I am ok with the code for that, if there is anohter method please can you expalin more.

    Thanks
    Roy

  • #4
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    Depends on the overall structure of the database and what you do with it, but chances are a single tinyint field in your database for isProcessed or something like that would suffice if you wanted to do it that way.
    Then its a matter of selecting all the records that have not been processed (make sure you store these primary keys, you'll want to execute an update on just the keys when done), and then update the records setting the isProcessed to true. Nothing else would change really.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

  • #5
    Regular Coder
    Join Date
    Jan 2010
    Posts
    161
    Thanks
    10
    Thanked 1 Time in 1 Post
    Hi

    I still seem to be having trouble with this date time but I now have two ways of getting the date from the VARCHAR table in the MySql query required format know I am stuck on how to add or subtract days from the date stored. I have shown the methods I have used and tried Fous methods but that just came back with numbers.

    What I want to be able to do is take the date that $final delivers and make a new variable called $final1 and add 3 days to the date on the order.

    My two methods are below:

    PHP Code:
    // Take Var Char and Convert
    $informat '%Y%m%d%H%M%S'
    $outformat =  '%Y-%m-%d';
    $ftime strptime ($resultset['datetime'],$informat);
    $unxTimestamp mktime
    $ftime['tm_hour'], 
    $ftime['tm_min'], 
    $ftime['tm_sec'], 
    $ftime['tm_yday'] + 1
    $ftime['tm_year'] + 1900 
    );
     
    $final strftime ($outformat$unxTimestamp); 
    Then to get into the XML I use the following

    $content .= "\t\t<INV_TIME>" . $final . "</INV_TIME>\n"; //yyyy-mm-dd format

    My second method is this by using the substr to get the results and then bring them back to the xml as follows:

    $content .= "\t\t<ORD_DATE>" . $orderyear ."-".$ordermonth."-".$orderday . "</ORD_DATE>\n"; //yyyy-mm-dd format

    PHP Code:
    $datetime $resultset['datetime'];
    $orderyear substr("$datetime"04);
    $ordermonth substr("$datetime"42);
    $orderday substr("$datetime"62); 
    Many Thanks
    Roy

  • #6
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    Correct, strtotime returns an integer representation of a timestamp.
    This can be formatted with the date() function, or extracted using the getdate() function if you want to easily separate the day month and year.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

  • #7
    Regular Coder
    Join Date
    Jan 2010
    Posts
    161
    Thanks
    10
    Thanked 1 Time in 1 Post
    Hi

    Not sure exactly what you mean here.

    This code works perfectly and provies the date from the VARCHAR being 20100813091426 to displaying in the XML as 2010-08-13.

    Now this is the order date to which depening on the delkivery date I want tp add either 1 day or 2 days etc using and if statement.

    So this code works perfectly for order date what sort of code would I need to add to make it add one day etc. In the XML I am just bringing out $final but want to make $final1 and $final2.

    PHP Code:
    // Take Var Char and Convert
    $informat '%Y%m%d%H%M%S'
    $outformat =  '%Y-%m-%d';
    $ftime strptime ($resultset['datetime'],$informat);
    $unxTimestamp mktime
    $ftime['tm_hour'], 
    $ftime['tm_min'], 
    $ftime['tm_sec'], 
    $ftime['tm_yday'] + 1
    $ftime['tm_year'] + 1900 
    );
     
    $final strftime ($outformat$unxTimestamp); 
    Thanks
    Roy

  • #8
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    I'm not sure we are on the same page here, so I'll post what I would do to see if its what you are looking for, and if not we can go from there.
    PHP Code:
    $format 'Y-m-d';
    $ftime strtotime($resultset['datetime']); // this is a timestamp.

    $threeDaysAgo strtotime('-3 day'$ftime); // Here we subtract 3 days
    $threeDaysAhead strtotime('+3 day'$ftime); // Here we add 3 days

    print date($format$threeDaysAgo); // The hours minutes and seconds should be the same just so you're aware
    print date($format$threeDaysAhead); 
    You are using strptime so you are on at least a 5.2 system. If you happen to have a 5.3 system you can also use the date_diff, date_add and date_sub (chained method functions) for simplicity.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •