• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Comparing Dates

 
Ranch Hand
Posts: 296
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I have setup a MySQL database with customer data, including a column which stores the "datetime" in which the record was inserted. To insert the date, I used SimpleDateFormat ("yyyy-MM-dd HH-mm-ss").

I only want to allow the client to add one new record per month to the database. If the record already exists in that month, then the record is just updated.

Can someone help me out with the SQL syntax I need to use to compare the current date with the date in the database?

Also, this needs to work indefintely. If I just compare months then when the next year rolls around I will end up overwriting the records, which is not what I want.

Any suggestions? Is there a better approach to doing this?

Regards,

Drew
 
Sheriff
Posts: 67746
173
Mac Mac OS X IntelliJ IDE jQuery TypeScript Java iOS
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
SimpleDateFormat is used to render a date for display. Are you using it to store the data in the DB as a string as opposed to an actual date?

If this your desired approach and you do not need the complete timestamp (i.e. the only thing you are using this for is to prevent duplicate monthly entries), I'd create a column to contain only the month/year and put a unique constraint on it.
[ February 14, 2005: Message edited by: Bear Bibeault ]
 
Drew Lane
Ranch Hand
Posts: 296
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Originally posted by Bear Bibeault:
SimpleDateFormat is used to render a date for display. Are you using it to store the data in the DB as a string as opposed to an actual date?

If this your desired approach and you do not need the complete timestamp (i.e. the only thing you are using this for is to prevent duplicate monthly entries), I'd create a column to contain only the month/year and put a unique constraint on it.

[ February 14, 2005: Message edited by: Bear Bibeault ]


Yes, I am using the SimpleDataFormat to prepare the java Date for storage in the DB. Is this not correct? It seemed to match the format required by MySQL. If there was a way to get MySQL to just automatically enter the "datetime" for me when I insert a row that would be even better but I'm not sure if that is possible. (?)

I need the time as well, because I sort my ResultsSet by date & time.

I'm not sure what you mean to "put a unique constraint on it".

Drew
 
Bear Bibeault
Sheriff
Posts: 67746
173
Mac Mac OS X IntelliJ IDE jQuery TypeScript Java iOS
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Yes, I am using the SimpleDataFormat to prepare the java Date for storage in the DB. Is this not correct?



Probably not. If the column is of data type TIMESTAMP (there's also DATETIME in MySQL), the simplest means to set the value is as follows:



This assumes that you are using a PreparedStatement, that theDateVariable is of type java.sql.Timestamp, and n is the 1-based position of the ? representing the date in your SQL statement.

If you are not using a PreparedStatement, but instead are just inlining the data using a String or StringBuffer, you are doing it wrong. If your SQL statement is parameterized, as is the case with your date, you should be using a PreparedStatement.

I need the time as well, because I sort my ResultsSet by date & time.



Sounds like you are making the column do double duty. This make it hard to use this data both for sorting on a complete timestamp as well as for the uniqueness check.


I'm not sure what you mean to "put a unique constraint on it".



If you are going to be working with databases, it's a good idea to become familiar with database concepts.

Check out Creating indexes in MySQL.
[ February 14, 2005: Message edited by: Bear Bibeault ]
 
Bear Bibeault
Sheriff
Posts: 67746
173
Mac Mac OS X IntelliJ IDE jQuery TypeScript Java iOS
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
But, a unique index won't be able to help you since you've got the whole timestamp in the column rather than just the data that needs to be unique (month and year).

You could add another column containing just month/year which has a unique index constraining it. But maybe someone more DB-savvy than myself can come up with something more clever (perhaps by adding a constraint using MySQL time functions that can extract just the month and year?)
[ February 14, 2005: Message edited by: Bear Bibeault ]
 
Drew Lane
Ranch Hand
Posts: 296
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I am using DATETIME, and MySQL seems to be happy with the way I'm doing it.

At least it's sorting my data correctly.

What if I add two extra columns, one for month, and one for year?

Doesn't seem very elegant, but I suppose it would work.

Or how about turning that DATETIME back into something I can play with in Java? Maybe the TIMESTAMP would be better for this?

Yes, you are right, I'm not a SQL guy - just trying to figure this out...

Drew
 
Ranch Hand
Posts: 1646
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
In Oracle you would use a function-based index just as Bear mentioned.That syntax probably isn't right (I haven't needed to use those functions in ages), but I always just look it up. MySQL actually has a very nice syntax reference posted on the main site, IIRC. I looked at it briefly once, and it was very thorough.

You should be able to continue getting away with a regular Statement, but I highly recommend taking a few hours some day and picking up PreparedStatements. They make things a bit easier as you don't have so much trouble building your SQL statements. For example, they take care of escaping special characters (') in your String parameters.

To bring this back to your original question, you should still be able to perform the "select count(*)" using the same functions you use to create the index.Replace <year> and <month> in your built SQL query with the integer values for the date you need. You can use Calendar for this.Oh, actually you can make this easier. Skip the Calendar and pass in today's datetime in the SQL query:This will tell you if there's already a row for that user and the current month/year. If so, perform an update, otherwise perform an insert.

The more I think about it, the more I would recommend going the other route if MySQL's JDBC driver supports updatable ResultSets. If so, select the values you want to update with the above query instead of the count. If you get no row, perform an update. If you get a row, update the values you want and send it back to the server. I haven't used updatable ResultSets yet, but others here will be able to help with that.

Heh, I could go either way. I'd have to see the resulting code and just make a gut feel call on it.

[ Bad smilies! ]
[ February 14, 2005: Message edited by: David Harkness ]
 
Ranch Hand
Posts: 1228
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Drew ,
I'm not sure whether i have understood you question correctly.
for the problem mentioned below,

I only want to allow the client to add one new record per month to the database. If the record already exists in that month, then the record is just updated.



Use a Stored procedure: Get the rowcount for the records inserted between start & end of this month. If it's Zero then insert a new record else update the already existing row.

Hi guys is this a correct approach ?
 
Drew Lane
Ranch Hand
Posts: 296
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
OK, I got this working (yay!) using the first method David suggested but I couldn't get a unique index setup (just a non unique index).

If I try to make the month_year index unique, it complains because there are several clients that have the same month/year entries (this is to be expected, BTW).

So what was the reason for making this index unique in the first place?

If the index is not unique will I still get the performance benefits of using an index?

Regards,

Drew

p.s. This stuff is a trip!
 
David Harkness
Ranch Hand
Posts: 1646
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Ah yes, you need to make the unique index be (user_id, month function, year function) to work, but you can pretty much do without it. The reason for a unique index (or unique constraint) is to disallow at the database invalid inserts.

As you have it, someone could connect to your database using some other application (like the interactive SQL tool) and insert duplicate entries. This may not be a concern for you, but you should be aware of the possibility.

As well, there is a race condition using the method you have. If multiple threads or applications try to perform the same entry insertion for a new month, you can end up with an error with this sequence.
  • Thread A queries; no record found; should insert.
  • Thread B queries; no record found; should insert.
  • Thread A performs insert.
  • Thread B performs insert.

  • Now you have two matching rows. Clearly, this would require near-split-second timing as well as multiple threads/apps which may not even be how your system is set up.

    By using a unique constraint, the 4th step would fail with a SQLException and that thread could retry the query, find the duplicate row, and perform an update instead. This provides you absolute certainty that no bogus data will be created.

    Some applications require absolute certainty; some do not. You need to make a judgment call on this.
     
    A timing clock, fuse wire, high explosives and a tiny ad:
    a bit of art, as a gift, that will fit in a stocking
    https://gardener-gift.com
    reply
      Bookmark Topic Watch Topic
    • New Topic