Zikula Community

• kylegio
  
  

  • Rank: Softmore
  • Registered: Sep 30, 2008
  • Last visit: May 29, 2010
  • Posts: 201

  Posted: Jun 13, 2011 - 05:06 AM

  hello, i need to use DQL to select records where more then X seconds has elapsed since updated_at
  
  my understanding is that the updated_at field is set when saving a record and uses PHP's date/time (as opposed to the databases date/time)
  
  most date/time comparison functions dont seem to work in DQL
  
  looking here: http://stackoverflow.com/questions/2365246/in-doctrine-orm-how-can-i-sum-seconds-to-a-timestamp-field
  
  theres an example of working with timestamps in DQL, but their example doesn't work because the function "DATE_ADD" doesnt exist.
  
  i need something along the lines of this:
  

  Code

  ->where("DATE_ADD(u.updated_at, INTERVAL 30 SECOND) <= ?", date('Y-m-d H:i:s', time()));

  
  "when record was updated more then 30 seconds ago." keep in mind that the timestamp format in the database is "Y-m-d H:i:s"
  
  
  
  any thoughts?

  • 
  •  
• craigh
  
  

  • Rank: Developer
  • Registered: Jun 16, 2003
  • Last visit: May 29, 2010
  • Posts: 1966

  Posted: Jun 13, 2011 - 07:57 AM

  can't you just do the computation in PHP and do something like
  
  

  Code

  ->where("u.updated <= ?", $timePlus30seconds);

  
  
  ?

  • 
  •  
• kylegio
  
  

  • Rank: Softmore
  • Registered: Sep 30, 2008
  • Last visit: May 29, 2010
  • Posts: 201

  Posted: Jun 13, 2011 - 08:15 AM

  i think there was a reason i had trouble with that, ill try it again when i get to my computer and see if i can get it to work.
  
  the issue might have been to do with the timestamp formatting.
  maybe something like
  

  Code

  ->where("u.updated <= ?", date('Y-m-d H:i:s', time()-30));

  • 
  •  
• kylegio
  
  

  • Rank: Softmore
  • Registered: Sep 30, 2008
  • Last visit: May 29, 2010
  • Posts: 201

  Posted: Jun 13, 2011 - 06:22 PM

  as i thought, i cant make that comparison because of the formatting, i think id need u.updated in unix time so i can make an comparison of two numbers, otherwise im comparing two strings and just not getting the result i need.
  
  does anyone have any ideas?

  • 
  •  
• craigh
  
  

  • Rank: Developer
  • Registered: Jun 16, 2003
  • Last visit: May 29, 2010
  • Posts: 1966

  Posted: Jun 14, 2011 - 12:55 AM

  I do it all the time in PC that way - just not with Doctrine. try putting quotes around the question mark lik
  
  

  Code

  ->where("u.updated <= '?'", date('Y-m-d H:i:s', time()-30));

  • 
  •  
• nestormateo
  
  

  • Rank: Team Member
  • Registered: Sep 06, 2006
  • Last visit: May 09, 2010
  • Posts: 2446

  Posted: Jun 14, 2011 - 02:21 AM

  Doctrine quotes que vaslues automagically.
  
  May be works the same way it handles NOW:
  

  Code

  Doctrine_Expression('NOW()')

  so you may need:
  

  Code

  Doctrine_Expression("'".date('Y-m-d H:i:s', time()-30)."'")

  
  I'm not sure, give it a shot.
  
  --
  - Mateo T. -
  Mis principios... son mis fines

  • 
  •  
• kylegio
  
  

  • Rank: Softmore
  • Registered: Sep 30, 2008
  • Last visit: May 29, 2010
  • Posts: 201

  Posted: Jun 14, 2011 - 06:57 AM

  Will give that a try and post back my findings. I find it annoying that doctrine keeps timestamps but offers now way of eaisly working with them. I mean i could select everything and make the comparison in PHP but why should i haveto increase the overhead by doing that just to solve what should be a simple issue.
  
  Kyle

  • 
  •  
• kylegio
  
  

  • Rank: Softmore
  • Registered: Sep 30, 2008
  • Last visit: May 29, 2010
  • Posts: 201

  Posted: Jun 14, 2011 - 09:52 AM

  mateo, thanks for the idea, but it didnt work either.
  
  to see how its making the comparison i issued the following in phpMyAdmin
  

  Code

  select (
  updated_at -  '2011-06-14 17:05:49'
  ) as outofdate, updated_at, NOW( ) as now, uid
  from  `z_zim_users`
  where 1
  limit 0 , 30

  
  

  Code

  outofdate        updated_at              now                    uid
  20110614198309    2011-06-14 20:03:20    2011-06-14 17:11:25    2

  
  
  
  we see the result above for 'outofdate' i need that to be in seconds essentially. the timestamp used in the SQL code above was generated using
  

  Code

  [new Doctrine_Expression("'".date('Y-m-d H:i:s', time()-30)."'");

  
  
  my complete statment is
  

  Code

  $q = Doctrine_Query::create()
          ->update('Zim_Model_User u')
          ->set('u.status',"?", '0')
          ->where("u.updated_at <= ?", new Doctrine_Expression("'".date('Y-m-d H:i:s', time()-30)."'"));

  
  
  
  i can't believe its this hard to make a DQL comparison of the updated timestamp to current timestamp.
  there has to be an easier way that actually works
  
  
  
  Edited by kylegio on Jun 14, 2011 - 06:32 PM.

  • 
  •  
• kylegio
  
  

  • Rank: Softmore
  • Registered: Sep 30, 2008
  • Last visit: May 29, 2010
  • Posts: 201

  Posted: Jun 14, 2011 - 10:21 AM

  ive discovered why the values were wrong in the database (off by 3 hours), there was some obscure piece of code somewhere in the module that was setting that value (incorrectly) and then saving it
  
  Seems to be working going back to the original
  

  Code

  ->where("u.updated_at <= ?", date('Y-m-d H:i:s', time()-30));

  
  and having my own bad fixed
  
  
  Thanks for the help, and sorry to waste anyones time with a stupid mistake i made somewhere
  
  
  
  Edited by kylegio on Jun 14, 2011 - 06:44 PM.

  • 
  •  
•  
•  

Users on-line

• 0 users

This list is based on users active over the last 60 minutes.