37904

Doctrine 2 - Filter results by a datetime field's date part

Question:

I have a doctrine entity called Szerzodes with a datetime field called exportalva. Is it possible somehow to get the records of which the exportalva field's date part is a given value? E.g I want to get the records that were exported on a given day, regardless of the time. I've tried to

$em->createQuery("SELECT sz FROM Szerzodes sz WHERE DATE(sz.exportalva) = '2012-05-17'")

but it failed as DATE() is not a known function for DQL. Now I temporarily use

$em->createQuery("SELECT sz FROM Szerzodes sz WHERE sz.exportalva LIKE '2012-05-17%'")

but it seems to be an ugly hack for me. Is there a better way to do this?

Answer1:

Considering you have a <em>DATETIME</em> field in you database, it is probably that using <em>LIKE</em> (this completely depends on the database and table's engine) you are not using algorithms that are optimized to DATE operations.

One solution is to specify the whole day (from the first to the last second of it) using the <strong>BETWEEN</strong> operator that is supported by <a href="http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/dql-doctrine-query-language.html#id2" rel="nofollow">DQL</a> as seen in it's EBNF:

$em->createQuery("SELECT sz FROM Szerzodes sz WHERE sz.exportalva BETWEEN '2012-05-17 00:00:00' AND '2012-05-17 23:59:59'")

If you really want to use a function for that, it is also a good idea to use a function always in the value and not the column used in the condition. Some databases (like MySQL) do not use indexes in columns modified by native functions. You can create a user function called <strong>DATE</strong> that does what you expect (it could easily do the before mentioned example above) using a <a href="http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/cookbook/dql-user-defined-functions.html" rel="nofollow">DQL User defined function</a>.

Recommend

  • CurrentDateTime() in EF
  • Morphia projection behavior for instance variable with default value
  • I'm moving from C to C++ and I don't get this creation of a class
  • Doctrine where in with Doctrine_Query
  • SWIG unsigned char and byte[]
  • ServiceStack - [Reference] or [Ignore]?
  • How to correctly get a WinForms Button control to draw custom text
  • How to replace TouchesBegan with UIGestureRecognizer
  • What's the name of this finding square root algorithm?
  • Cast between interfaces whose interface signatures are same
  • Extract zip entries to another Zip file
  • Problems installing Yesod for Haskell
  • Unable to install Git-core+svn by MacPorts
  • ActiveRecord query for a count of new users by day
  • VSO Build — Response status code does not indicate success: 404 (Not Found)
  • why overloaded new operator is calling constructor even I am using malloc inside overloading functio
  • ilmerge with a PFX file
  • QLineEdit password safety
  • Illegal mix of collations for operation for date/time comparison
  • How to extract text from Word files using C#?
  • How to check if every primary key value is being referenced as foreign key in another table
  • Sending data from AppleScript to FileMaker records
  • Release, debug version and Authorization Google?
  • ORA-29908: missing primary invocation for ancillary operator
  • How to get next/previous record number?
  • using conditional logic : check if record exists; if it does, update it, if not, create it
  • Hits per day in Google Big Query
  • How do you join a server to an Active Directory (domain)?
  • How does Linux kernel interrupt the application?
  • IndexOutOfRangeException on multidimensional array despite using GetLength check
  • How to get NHibernate ISession to cache entity not retrieved by primary key
  • Binding checkboxes to object values in AngularJs
  • Observable and ngFor in Angular 2
  • How to Embed XSL into XML
  • UserPrincipal.Current returns apppool on IIS
  • Conditional In-Line CSS for IE and Others?
  • Net Present Value in Excel for Grouped Recurring CF
  • How can I use threading to 'tick' a timer to be accessed by other threads?
  • jQuery Masonry / Isotope and fluid images: Momentary overlap on window resize
  • How to load view controller without button in storyboard?