Magento checking order updated_at using Magento date and time












4














we are trying to filter out orders that were created longer than 60 minutes ago with status pending. We are having some problems with the date/time functions



We tried:
- date('Y-m-d H:m:s') and subtracting hours
- and Mage::getModel('core/date')



But both are not giving me the correct date/time as I see is being used for the column updated_at.



Question: What date/time function or method should I use to find a date-time that corresponds to the date-time that is used for the updated_at column in orders? (so when I have the correct date-time I can subtract and select orders)



$date = $this->get_offset_hours();
$orders = Mage::getModel('sales/order')->getCollection()
->addFieldToFilter('state', 'new')
->addFieldToFilter('status', 'pending')
->addFieldToFilter('updated_at', array('lt' => $date));









share|improve this question
















bumped to the homepage by Community yesterday


This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.




















    4














    we are trying to filter out orders that were created longer than 60 minutes ago with status pending. We are having some problems with the date/time functions



    We tried:
    - date('Y-m-d H:m:s') and subtracting hours
    - and Mage::getModel('core/date')



    But both are not giving me the correct date/time as I see is being used for the column updated_at.



    Question: What date/time function or method should I use to find a date-time that corresponds to the date-time that is used for the updated_at column in orders? (so when I have the correct date-time I can subtract and select orders)



    $date = $this->get_offset_hours();
    $orders = Mage::getModel('sales/order')->getCollection()
    ->addFieldToFilter('state', 'new')
    ->addFieldToFilter('status', 'pending')
    ->addFieldToFilter('updated_at', array('lt' => $date));









    share|improve this question
















    bumped to the homepage by Community yesterday


    This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.


















      4












      4








      4







      we are trying to filter out orders that were created longer than 60 minutes ago with status pending. We are having some problems with the date/time functions



      We tried:
      - date('Y-m-d H:m:s') and subtracting hours
      - and Mage::getModel('core/date')



      But both are not giving me the correct date/time as I see is being used for the column updated_at.



      Question: What date/time function or method should I use to find a date-time that corresponds to the date-time that is used for the updated_at column in orders? (so when I have the correct date-time I can subtract and select orders)



      $date = $this->get_offset_hours();
      $orders = Mage::getModel('sales/order')->getCollection()
      ->addFieldToFilter('state', 'new')
      ->addFieldToFilter('status', 'pending')
      ->addFieldToFilter('updated_at', array('lt' => $date));









      share|improve this question















      we are trying to filter out orders that were created longer than 60 minutes ago with status pending. We are having some problems with the date/time functions



      We tried:
      - date('Y-m-d H:m:s') and subtracting hours
      - and Mage::getModel('core/date')



      But both are not giving me the correct date/time as I see is being used for the column updated_at.



      Question: What date/time function or method should I use to find a date-time that corresponds to the date-time that is used for the updated_at column in orders? (so when I have the correct date-time I can subtract and select orders)



      $date = $this->get_offset_hours();
      $orders = Mage::getModel('sales/order')->getCollection()
      ->addFieldToFilter('state', 'new')
      ->addFieldToFilter('status', 'pending')
      ->addFieldToFilter('updated_at', array('lt' => $date));






      magento-1 collection date timezone






      share|improve this question















      share|improve this question













      share|improve this question




      share|improve this question








      edited May 16 '17 at 2:59









      sv3n

      9,66062352




      9,66062352










      asked Jan 30 '16 at 19:26









      snh_nlsnh_nl

      2,8531045101




      2,8531045101





      bumped to the homepage by Community yesterday


      This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.







      bumped to the homepage by Community yesterday


      This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
























          1 Answer
          1






          active

          oldest

          votes


















          0














          All magento transaction data it's saved in UTC so you need to start with the UTC now not your store or server now.



          To get the NOW in UTC you can use:



          // Get Store ID
          $store = $this->getCurrentStoreId(); // You can change it

          // Get Store Now (Zend_Date)
          $nowStore = Mage::app()->getLocale()
          ->storeDate($store, null, true);

          // Get Store Now (String)
          $nowStoreDatetime = $nowStore->toString('y-MM-dd HH:mm:ss');

          // Get Now UTC
          $nowUtc = Mage::app()->getLocale()
          ->utcDate($store, $date, true);


          Now you can play with Zend_Date:



          /** @var Zend_Date $nowUtc */

          $nowUtc->subHour(1);
          $finalDatetime = $nowUtc->toString('y-MM-dd HH:mm:ss');


          Using $finalDatetimein your query you should get the orders you want.



          Note that updated_at date can change if you add a comment to the order or some cron/payment gateway do something, so if you want to track the exactly moment that the order was set to 'pending' you should add a join with status_history table. If that were your case, just let me know and i'll update the answer.



          EDIT



          Adding a Join with Sales_Order_Status_History to filter by the date when the order was set as 'pending'.



              $orders = Mage::getModel('sales/order')->getCollection()
          $select = $orders->getSelect();
          $select->join(
          array('sfosh' => 'sales_flat_order_status_history'),
          'sfosh.parent_id = main_table.entity_id',
          array()
          )
          ->group('main_table.entity_id');

          $orders
          ->setOrder('sfosh.created_at', 'ASC')
          ->addFieldToFilter('sfosh.created_at', array('lteq' => $finalDatetime))
          ->addFieldToFilter('main_table.status', array('eq' => 'pending'))
          ;





          share|improve this answer























          • Yep ;) The exact moment status was set. Also we use this to release pending orders (release THE stock). So in essence i Would have to loop over all stores and get the date then offset it and then compare to the order per store? Just to release all pending orders order than 1 hour .... hmmm (super thanks btw)
            – snh_nl
            Jan 30 '16 at 21:04










          • mmm I think that you must to do it for each store (if they have different timezones), :( i'll update the answer with the status history join
            – MauroNigrele
            Jan 30 '16 at 21:13










          • hey i've just updated the answer. BTW tired of deal with this kind of issues i made a date helper a few years ago i've just uploaded to gist here gist.github.com/MauroNigrele/27ca96ab2eb58e462e4c fell free to use it. Using that helper you can grab the current UTC date just calling Mage::helper('vendor_module/date')->getNowUtc($store)
            – MauroNigrele
            Jan 30 '16 at 21:30











          Your Answer








          StackExchange.ready(function() {
          var channelOptions = {
          tags: "".split(" "),
          id: "479"
          };
          initTagRenderer("".split(" "), "".split(" "), channelOptions);

          StackExchange.using("externalEditor", function() {
          // Have to fire editor after snippets, if snippets enabled
          if (StackExchange.settings.snippets.snippetsEnabled) {
          StackExchange.using("snippets", function() {
          createEditor();
          });
          }
          else {
          createEditor();
          }
          });

          function createEditor() {
          StackExchange.prepareEditor({
          heartbeatType: 'answer',
          autoActivateHeartbeat: false,
          convertImagesToLinks: false,
          noModals: true,
          showLowRepImageUploadWarning: true,
          reputationToPostImages: null,
          bindNavPrevention: true,
          postfix: "",
          imageUploader: {
          brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
          contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
          allowUrls: true
          },
          onDemand: true,
          discardSelector: ".discard-answer"
          ,immediatelyShowMarkdownHelp:true
          });


          }
          });














          draft saved

          draft discarded


















          StackExchange.ready(
          function () {
          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fmagento.stackexchange.com%2fquestions%2f99720%2fmagento-checking-order-updated-at-using-magento-date-and-time%23new-answer', 'question_page');
          }
          );

          Post as a guest















          Required, but never shown

























          1 Answer
          1






          active

          oldest

          votes








          1 Answer
          1






          active

          oldest

          votes









          active

          oldest

          votes






          active

          oldest

          votes









          0














          All magento transaction data it's saved in UTC so you need to start with the UTC now not your store or server now.



          To get the NOW in UTC you can use:



          // Get Store ID
          $store = $this->getCurrentStoreId(); // You can change it

          // Get Store Now (Zend_Date)
          $nowStore = Mage::app()->getLocale()
          ->storeDate($store, null, true);

          // Get Store Now (String)
          $nowStoreDatetime = $nowStore->toString('y-MM-dd HH:mm:ss');

          // Get Now UTC
          $nowUtc = Mage::app()->getLocale()
          ->utcDate($store, $date, true);


          Now you can play with Zend_Date:



          /** @var Zend_Date $nowUtc */

          $nowUtc->subHour(1);
          $finalDatetime = $nowUtc->toString('y-MM-dd HH:mm:ss');


          Using $finalDatetimein your query you should get the orders you want.



          Note that updated_at date can change if you add a comment to the order or some cron/payment gateway do something, so if you want to track the exactly moment that the order was set to 'pending' you should add a join with status_history table. If that were your case, just let me know and i'll update the answer.



          EDIT



          Adding a Join with Sales_Order_Status_History to filter by the date when the order was set as 'pending'.



              $orders = Mage::getModel('sales/order')->getCollection()
          $select = $orders->getSelect();
          $select->join(
          array('sfosh' => 'sales_flat_order_status_history'),
          'sfosh.parent_id = main_table.entity_id',
          array()
          )
          ->group('main_table.entity_id');

          $orders
          ->setOrder('sfosh.created_at', 'ASC')
          ->addFieldToFilter('sfosh.created_at', array('lteq' => $finalDatetime))
          ->addFieldToFilter('main_table.status', array('eq' => 'pending'))
          ;





          share|improve this answer























          • Yep ;) The exact moment status was set. Also we use this to release pending orders (release THE stock). So in essence i Would have to loop over all stores and get the date then offset it and then compare to the order per store? Just to release all pending orders order than 1 hour .... hmmm (super thanks btw)
            – snh_nl
            Jan 30 '16 at 21:04










          • mmm I think that you must to do it for each store (if they have different timezones), :( i'll update the answer with the status history join
            – MauroNigrele
            Jan 30 '16 at 21:13










          • hey i've just updated the answer. BTW tired of deal with this kind of issues i made a date helper a few years ago i've just uploaded to gist here gist.github.com/MauroNigrele/27ca96ab2eb58e462e4c fell free to use it. Using that helper you can grab the current UTC date just calling Mage::helper('vendor_module/date')->getNowUtc($store)
            – MauroNigrele
            Jan 30 '16 at 21:30
















          0














          All magento transaction data it's saved in UTC so you need to start with the UTC now not your store or server now.



          To get the NOW in UTC you can use:



          // Get Store ID
          $store = $this->getCurrentStoreId(); // You can change it

          // Get Store Now (Zend_Date)
          $nowStore = Mage::app()->getLocale()
          ->storeDate($store, null, true);

          // Get Store Now (String)
          $nowStoreDatetime = $nowStore->toString('y-MM-dd HH:mm:ss');

          // Get Now UTC
          $nowUtc = Mage::app()->getLocale()
          ->utcDate($store, $date, true);


          Now you can play with Zend_Date:



          /** @var Zend_Date $nowUtc */

          $nowUtc->subHour(1);
          $finalDatetime = $nowUtc->toString('y-MM-dd HH:mm:ss');


          Using $finalDatetimein your query you should get the orders you want.



          Note that updated_at date can change if you add a comment to the order or some cron/payment gateway do something, so if you want to track the exactly moment that the order was set to 'pending' you should add a join with status_history table. If that were your case, just let me know and i'll update the answer.



          EDIT



          Adding a Join with Sales_Order_Status_History to filter by the date when the order was set as 'pending'.



              $orders = Mage::getModel('sales/order')->getCollection()
          $select = $orders->getSelect();
          $select->join(
          array('sfosh' => 'sales_flat_order_status_history'),
          'sfosh.parent_id = main_table.entity_id',
          array()
          )
          ->group('main_table.entity_id');

          $orders
          ->setOrder('sfosh.created_at', 'ASC')
          ->addFieldToFilter('sfosh.created_at', array('lteq' => $finalDatetime))
          ->addFieldToFilter('main_table.status', array('eq' => 'pending'))
          ;





          share|improve this answer























          • Yep ;) The exact moment status was set. Also we use this to release pending orders (release THE stock). So in essence i Would have to loop over all stores and get the date then offset it and then compare to the order per store? Just to release all pending orders order than 1 hour .... hmmm (super thanks btw)
            – snh_nl
            Jan 30 '16 at 21:04










          • mmm I think that you must to do it for each store (if they have different timezones), :( i'll update the answer with the status history join
            – MauroNigrele
            Jan 30 '16 at 21:13










          • hey i've just updated the answer. BTW tired of deal with this kind of issues i made a date helper a few years ago i've just uploaded to gist here gist.github.com/MauroNigrele/27ca96ab2eb58e462e4c fell free to use it. Using that helper you can grab the current UTC date just calling Mage::helper('vendor_module/date')->getNowUtc($store)
            – MauroNigrele
            Jan 30 '16 at 21:30














          0












          0








          0






          All magento transaction data it's saved in UTC so you need to start with the UTC now not your store or server now.



          To get the NOW in UTC you can use:



          // Get Store ID
          $store = $this->getCurrentStoreId(); // You can change it

          // Get Store Now (Zend_Date)
          $nowStore = Mage::app()->getLocale()
          ->storeDate($store, null, true);

          // Get Store Now (String)
          $nowStoreDatetime = $nowStore->toString('y-MM-dd HH:mm:ss');

          // Get Now UTC
          $nowUtc = Mage::app()->getLocale()
          ->utcDate($store, $date, true);


          Now you can play with Zend_Date:



          /** @var Zend_Date $nowUtc */

          $nowUtc->subHour(1);
          $finalDatetime = $nowUtc->toString('y-MM-dd HH:mm:ss');


          Using $finalDatetimein your query you should get the orders you want.



          Note that updated_at date can change if you add a comment to the order or some cron/payment gateway do something, so if you want to track the exactly moment that the order was set to 'pending' you should add a join with status_history table. If that were your case, just let me know and i'll update the answer.



          EDIT



          Adding a Join with Sales_Order_Status_History to filter by the date when the order was set as 'pending'.



              $orders = Mage::getModel('sales/order')->getCollection()
          $select = $orders->getSelect();
          $select->join(
          array('sfosh' => 'sales_flat_order_status_history'),
          'sfosh.parent_id = main_table.entity_id',
          array()
          )
          ->group('main_table.entity_id');

          $orders
          ->setOrder('sfosh.created_at', 'ASC')
          ->addFieldToFilter('sfosh.created_at', array('lteq' => $finalDatetime))
          ->addFieldToFilter('main_table.status', array('eq' => 'pending'))
          ;





          share|improve this answer














          All magento transaction data it's saved in UTC so you need to start with the UTC now not your store or server now.



          To get the NOW in UTC you can use:



          // Get Store ID
          $store = $this->getCurrentStoreId(); // You can change it

          // Get Store Now (Zend_Date)
          $nowStore = Mage::app()->getLocale()
          ->storeDate($store, null, true);

          // Get Store Now (String)
          $nowStoreDatetime = $nowStore->toString('y-MM-dd HH:mm:ss');

          // Get Now UTC
          $nowUtc = Mage::app()->getLocale()
          ->utcDate($store, $date, true);


          Now you can play with Zend_Date:



          /** @var Zend_Date $nowUtc */

          $nowUtc->subHour(1);
          $finalDatetime = $nowUtc->toString('y-MM-dd HH:mm:ss');


          Using $finalDatetimein your query you should get the orders you want.



          Note that updated_at date can change if you add a comment to the order or some cron/payment gateway do something, so if you want to track the exactly moment that the order was set to 'pending' you should add a join with status_history table. If that were your case, just let me know and i'll update the answer.



          EDIT



          Adding a Join with Sales_Order_Status_History to filter by the date when the order was set as 'pending'.



              $orders = Mage::getModel('sales/order')->getCollection()
          $select = $orders->getSelect();
          $select->join(
          array('sfosh' => 'sales_flat_order_status_history'),
          'sfosh.parent_id = main_table.entity_id',
          array()
          )
          ->group('main_table.entity_id');

          $orders
          ->setOrder('sfosh.created_at', 'ASC')
          ->addFieldToFilter('sfosh.created_at', array('lteq' => $finalDatetime))
          ->addFieldToFilter('main_table.status', array('eq' => 'pending'))
          ;






          share|improve this answer














          share|improve this answer



          share|improve this answer








          edited Jan 30 '16 at 21:21

























          answered Jan 30 '16 at 20:10









          MauroNigreleMauroNigrele

          2,577926




          2,577926












          • Yep ;) The exact moment status was set. Also we use this to release pending orders (release THE stock). So in essence i Would have to loop over all stores and get the date then offset it and then compare to the order per store? Just to release all pending orders order than 1 hour .... hmmm (super thanks btw)
            – snh_nl
            Jan 30 '16 at 21:04










          • mmm I think that you must to do it for each store (if they have different timezones), :( i'll update the answer with the status history join
            – MauroNigrele
            Jan 30 '16 at 21:13










          • hey i've just updated the answer. BTW tired of deal with this kind of issues i made a date helper a few years ago i've just uploaded to gist here gist.github.com/MauroNigrele/27ca96ab2eb58e462e4c fell free to use it. Using that helper you can grab the current UTC date just calling Mage::helper('vendor_module/date')->getNowUtc($store)
            – MauroNigrele
            Jan 30 '16 at 21:30


















          • Yep ;) The exact moment status was set. Also we use this to release pending orders (release THE stock). So in essence i Would have to loop over all stores and get the date then offset it and then compare to the order per store? Just to release all pending orders order than 1 hour .... hmmm (super thanks btw)
            – snh_nl
            Jan 30 '16 at 21:04










          • mmm I think that you must to do it for each store (if they have different timezones), :( i'll update the answer with the status history join
            – MauroNigrele
            Jan 30 '16 at 21:13










          • hey i've just updated the answer. BTW tired of deal with this kind of issues i made a date helper a few years ago i've just uploaded to gist here gist.github.com/MauroNigrele/27ca96ab2eb58e462e4c fell free to use it. Using that helper you can grab the current UTC date just calling Mage::helper('vendor_module/date')->getNowUtc($store)
            – MauroNigrele
            Jan 30 '16 at 21:30
















          Yep ;) The exact moment status was set. Also we use this to release pending orders (release THE stock). So in essence i Would have to loop over all stores and get the date then offset it and then compare to the order per store? Just to release all pending orders order than 1 hour .... hmmm (super thanks btw)
          – snh_nl
          Jan 30 '16 at 21:04




          Yep ;) The exact moment status was set. Also we use this to release pending orders (release THE stock). So in essence i Would have to loop over all stores and get the date then offset it and then compare to the order per store? Just to release all pending orders order than 1 hour .... hmmm (super thanks btw)
          – snh_nl
          Jan 30 '16 at 21:04












          mmm I think that you must to do it for each store (if they have different timezones), :( i'll update the answer with the status history join
          – MauroNigrele
          Jan 30 '16 at 21:13




          mmm I think that you must to do it for each store (if they have different timezones), :( i'll update the answer with the status history join
          – MauroNigrele
          Jan 30 '16 at 21:13












          hey i've just updated the answer. BTW tired of deal with this kind of issues i made a date helper a few years ago i've just uploaded to gist here gist.github.com/MauroNigrele/27ca96ab2eb58e462e4c fell free to use it. Using that helper you can grab the current UTC date just calling Mage::helper('vendor_module/date')->getNowUtc($store)
          – MauroNigrele
          Jan 30 '16 at 21:30




          hey i've just updated the answer. BTW tired of deal with this kind of issues i made a date helper a few years ago i've just uploaded to gist here gist.github.com/MauroNigrele/27ca96ab2eb58e462e4c fell free to use it. Using that helper you can grab the current UTC date just calling Mage::helper('vendor_module/date')->getNowUtc($store)
          – MauroNigrele
          Jan 30 '16 at 21:30


















          draft saved

          draft discarded




















































          Thanks for contributing an answer to Magento Stack Exchange!


          • Please be sure to answer the question. Provide details and share your research!

          But avoid



          • Asking for help, clarification, or responding to other answers.

          • Making statements based on opinion; back them up with references or personal experience.


          To learn more, see our tips on writing great answers.





          Some of your past answers have not been well-received, and you're in danger of being blocked from answering.


          Please pay close attention to the following guidance:


          • Please be sure to answer the question. Provide details and share your research!

          But avoid



          • Asking for help, clarification, or responding to other answers.

          • Making statements based on opinion; back them up with references or personal experience.


          To learn more, see our tips on writing great answers.




          draft saved


          draft discarded














          StackExchange.ready(
          function () {
          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fmagento.stackexchange.com%2fquestions%2f99720%2fmagento-checking-order-updated-at-using-magento-date-and-time%23new-answer', 'question_page');
          }
          );

          Post as a guest















          Required, but never shown





















































          Required, but never shown














          Required, but never shown












          Required, but never shown







          Required, but never shown

































          Required, but never shown














          Required, but never shown












          Required, but never shown







          Required, but never shown







          Popular posts from this blog

          1300-talet

          1300-talet

          Display a custom attribute below product name in the front-end Magento 1.9.3.8