Creating only month and year column in MySQL database

Question

I'm creating a little program with Java. In the date field which is a ComboBox for selecting only month and date as 02/2016.

Is there a way to record this date format in MySQL in a mm/yy field only?

Answer 1

Instead of storing it in this format, maybe the best approach is to store it as a normal date with the day always set to the first day of the month. And then at retrieval time you format the date, with something like a SimpleDateFormat, to only get the month and year and return it to your UI.

Answer 2

You shouldn't do that, and there's no native way to do so. Just save it as a normal DATE in MySQL and, when you query the database SELECT MONTH(datefield), YEAR(datefield) or parse the date with Java.

The MySQL date and datetime types consist of a date including a day (or including a time). In order to be able to query for a range and other date related stuff, you should stick to the intended way of storing dates. MySQL has excellent datetime functions, you shouldn't do something that takes them away from you.

Answer 3

ISO 8601

The ISO 8601 standard defines many formats for strings representing date-time values. One of them is for a year and a month without any date, without a time zone, and without a time-of-day: YYYY-MM. Example: 2016-02 for February 2016.

Database storage

Save these standard strings in a textual column in your database. Note that sorting alphabetically also means chronological ordering.

Java

The java.time classes use ISO 8601 standard formats by default when parsing/generating strings that represent date-time values.

The YearMonth class represents a year-and-month values without a date. Within your app, use these objects rather than mere strings or numbers. Doing so provides type-safety, ensures valid values, and makes your code more self-documenting.

YearMonth ym = YearMonth.of( 2016 , 2 );
String output = ym.toString();  // 2016-02

And going the other direction, parsing a standard string.

YearMonth ym = YearMonth.parse( "2016-02" );

Queries

You can perform textual queries as the standard formats are chronological when sorted alphabetically. The people on that ISO committee are so clever.

For example, to query for values in the first quarter of the year 2016:

WHERE expiration >= '2016-01' 
AND expiration <= '2016-03'

Also, in date-time work we usually use Half-Open approach to spans of time where the beginning is inclusive while the ending is exclusive.

WHERE expiration >= '2016-01' 
AND expiration < '2016-04'  // Up to, but not including, the month at end of this span.

Of course, you really should be using PreparedStatement rather than raw SQL query.

YearMonth q2016_ymStart = YearMonth.of( 2016 , 1 );  // 2016-01
YearMonth q2016_ymStop = q2016_ymStart.plusMonths( 3 );  // 2016-04 for half-open quarter.
…
myPreparedStatement.setString( … , q2016_ymStart.toString() );
myPreparedStatement.setString( … , q2016_ymStop.toString() );

And speaking of quarters, see the ThreeTen-Extra project for YearQuarter and Quarter classes.

LocalDate

To transform a YearMonth to a date, use the LocalDate class.

LocalDate firstOfMonth = ym.atDay( 1 );
LocalDate endOfMonth = ym.atEndOfMonth();

ZonedDateTime

To transform a YearMonth into a moment on the timeline, specify a time zone for the context intended by your business scenario. Let java.time determine the first moment of the day, as the first moment is not always 00:00:00.

ZoneId z = ZoneId.of( "America/Montreal" );
ZonedDateTime zdt = ym.atDay( 1 ).atStartOfDay( z );

Database access

For database access, your JDBC 4.2 compliant driver may be able to handle these java.time types directly by calling the PreparedStatement::setObject and ResultSet::getObject methods.

If not, fall back to converting the java.time types to the old java.sql types using new methods added to those old classes. Search Stack Overflow for much more info.

---

About java.time

The java.time framework is built into Java 8 and later. These classes supplant the troublesome old legacy date-time classes such as java.util.Date, Calendar, & SimpleDateFormat.

The Joda-Time project, now in maintenance mode, advises migration to java.time.

To learn more, see the Oracle Tutorial. And search Stack Overflow for many examples and explanations. Specification is JSR 310.

Where to obtain the java.time classes?

• Java SE 8 and SE 9 and later
  • Built-in.
  • Part of the standard Java API with a bundled implementation.
  • Java 9 adds some minor features and fixes.
• Java SE 6 and SE 7
  • Much of the java.time functionality is back-ported to Java 6 & 7 in ThreeTen-Backport.
• Android
  • The ThreeTenABP project adapts ThreeTen-Backport (mentioned above) for Android specifically.
  • See How to use….

The ThreeTen-Extra project extends java.time with additional classes. This project is a proving ground for possible future additions to java.time. You may find some useful classes here such as Interval, YearWeek, YearQuarter, and more.