Solved

MySql Date Query using Hibernate

Posted on 2004-10-29
551 Views
Last Modified: 2012-05-05
The following two querystrings were used in my code to pull up objects which had date values greater than those specified.  I'm unsure how to format the date to query MySql I guess because both of these pull up every record regardless of date.  Any suggestions?

from com.PackageNamesHere as code where code.StartTime > 'Wed Oct 27 21:47:10 EST 2004'
from com.PackageNamesHere as code where code.StartTime > '1098931590421'

Thanks,
David
0
Question by:lomidien
    15 Comments
     
    LVL 8

    Expert Comment

    by:kiranhk
    Did you check this out

    http://www.hibernate.org/80.html

    Microsoft SQL Server
    License:  Commercial
    URL:  http://www.microsoft.com/sql/default.asp
    Issues:  Version supported: 2000, but creating a dialect for version 7 should be easy.
     JDBC drivers: jTDS, open source, LGPL: also used with Sybase. If you are using triggers, Hibernate will get confused when the first result returned by a statement is "0" (in jTDS, use the parameter lastUpdateCount=true in the driver -- this is the default value, anyway).
     The following commercial drivers are known to work: JSQL , JTurbo, Weblogic jDriver. It is quite likely that you will be able to use DataDirect Connect for JDBC. There are some weird date-handling issues with this driver, so it is not officially supported. Don't bother with the DataDirect SequeLink driver; it has very serious problems. We have had great luck (not a single issue) with the inet drivers but they are relatively expensive.  
     Use some driver other than Microsoft's own! If you use the MS JDBC driver, these are the issues you will face: Blobs and Clobs are not supported. Problems with Dates: MSSQL when you search for dates wants to search by seconds and milliseconds. When you convert a SQL date to a Java Timestamp object, and then back again, you seem to lose the milliseconds. The seconds match, but not the milliseconds. So don't use date's as part of a composite key/primary key. Reportedly, the Microsoft JDBC driver is quite slow, you have to add sendStringParametersAsUnicode=false to JDBC URL to increase performance. You must always use selectMethod=cursor. You may get a "Reread" error when reading tables with NVARCHAR or Images. You can solve that issue by proxying the MS JDBC Driver: MSJdbcProxy.
    0
     
    LVL 37

    Expert Comment

    by:zzynx
    Try

    from com.PackageNamesHere as code where code.StartTime > '2004-10-27'
    0
     

    Author Comment

    by:lomidien
    It's actually MySql server, so I'm not sure if that url applies.  :(

    zzynx,

    I tried that, but not much luck...I've also tried the queries I posted but minus the "'" on each end of the date.  I'm stressing now....very little left on this to go and my deployment is supposed to be today.  YIKES!!!!!

    Any other ideas?

    Thanks,
    David
    0
     
    LVL 37

    Expert Comment

    by:zzynx
    forgot the time part:
         from com.PackageNamesHere as code where code.StartTime > '2004-10-27 21:47:10'
    0
     
    LVL 37

    Expert Comment

    by:zzynx
    What type is StartTime?

    DATE: YYYY-MM-DD (Four digit year followed by two digit month and date)
    TIME: hh:mm:ss (Hours:Minutes:Seconds)
    DATETIME: YYYY-MM-DD hh:mm:ss (Date and time separated by a space character)
    TIMESTAMP: YYYYMMDDhhmmss
    YEAR: YYYY (4 digit year)
    0
     

    Author Comment

    by:lomidien
    Sorry, forgot that tidbit of info...might have been helpful, huh?  :)

    StartTime is a Date().
    0
     
    LVL 8

    Expert Comment

    by:kiranhk
    sorry, i read it as MS SQL
    0
     

    Author Comment

    by:lomidien
    kiranhk,

    Yeah, in fact I came across that page when I googled and had read it just before you posted.  Then I reread thinking I had missed something.  :)

    I've always hated working with dates!!!!!

    David
    0
     
    LVL 37

    Assisted Solution

    by:zzynx
    >> StartTime is a Date().
    Well at http://www.webdevelopersnotes.com/tutorials/sql/mysql_development_tutorial_mysql_date_column_type_part_1.php3 I read:

    Characteristics of Date
    MySQL dates are ALWAYS represented with the year followed by the month and then the date.
    Often you'll find dates written as YYYY-MM-DD, where YYYY is 4 digit year, MM is 2 digit month and DD, 2 digit date.

    So...
    0
     

    Author Comment

    by:lomidien
    I tried the query below:

    select * from dtevent where starttime > '2004-10-27 21:47:10'

    using straight JDBC and this as my query string and it works fine, however using what we had posted above and with the correct date formatting from the JDBC sql line, it returns all records.  I would have thought that the HQL implementation for date queries would function almost identically, this must not be the case.

    Still stuck.  :(

    David
    0
     

    Author Comment

    by:lomidien
    I would use JDBC to query my results, but that breaks my JTable code which reads in objects mapped from Hibernate.
    0
     
    LVL 8

    Expert Comment

    by:kiranhk
    can you post your hibernate mapping xml
    0
     
    LVL 8

    Accepted Solution

    by:
    0
     

    Author Comment

    by:lomidien
    If I used a NamedQuery posted within the mapping document, it will work just fine.  I'm sure it has to do with Hibernate's handling of converting java.util.Date and because using straight HQL, I was passing in a string, it was not quite jiving for whatever reason.  Who knows, but not that it's mapped in a file and not hard-coded, it works fine.

    How about I split the points for you guys?

    Thanks,
    David
    0
     
    LVL 37

    Expert Comment

    by:zzynx
    Thanks
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone. Privacy Policy Terms of Use

    Featured Post

    Anonabox PRO Tor & VPN Router

    PRO is the most advanced way to fortify your privacy and online anonymity by layering the Tor network with VPN services. Use both together or separately, and without needing to download software onto your devices.

    After being asked a question last year, I went into one of my moods where I did some research and code just for the fun and learning of it all.  Subsequently, from this journey, I put together this article on "Range Searching Using Visual Basic.NET …
    I had a project requirement for a displaying a user workbench .This workbench would consist multiple data grids .In each grid the user will be able to see a large number of data. These data grids should allow the user to 1. Sort 2. Export the …
    Viewers will learn about the different types of variables in Java and how to declare them. Decide the type of variable desired: Put the keyword corresponding to the type of variable in front of the variable name: Use the equal sign to assign a v…
    The viewer will learn how to implement Singleton Design Pattern in Java.

    860 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now