CODEWITHSUNDEEP
phpmysqlsqldate
J.K.A.
J.K.A.

Reputation: 7404

Store month and year in mysql database

I have following date: 

$params['ExpirationMonth'];
$params['ExpirationYear'];

I need some suggestion regarding how can I store only month and year in mysql database. After some research I have found 3 ways to do it.

1) I can use 1st as the days and can store along with month and year.

2) Instead of using date I can use varchar datatype and can only store month and year in it.

3) I can store the two as two separate columns as integers. i.e. for month and year.

Which is the better way?. Need some suggestion. Thanks.

Upvotes: 4

Views: 7238

Answers (5)

Saeed Farahi
Saeed Farahi

Reputation: 111

You can always store it like YYYYDD as an integer: So for May 2021 you can store this integer: 202105

Upvotes: -1

Salman Arshad
Salman Arshad

Reputation: 272036

You can use a date column, keeping in mind that:

  • You could set the day portion to 1
  • The card is good for the entire month of the specified expiry date. 0512 means valid through 31 May 2012.

Advantages:

  • Sorting is straight forward (compared to storing as varchar)
  • Date operations are straight forward (compared to storing as varchar/two integer columns)
  • Data stays in one column (compared to storing as two integer columns)

Sample queries:

-- expires in 2012
WHERE expires >= '2012-01-01' AND expires < '2012-01-01' + INTERVAL 1 YEAR
-- expires in Dec 2012
WHERE expires >= '2012-12-01' AND expires < '2012-12-01' + INTERVAL 1 MONTH

Upvotes: 2

Sir Rufo
Sir Rufo

Reputation: 19096

Expiration Month/Year from credit cards means that this card is valid until the end of this Month/Year.

Best way to store as a comparable date you should get the last day of this month

STR_TO_DATE( CONCAT( '01,', @Month, ',', @Year, '%d,%m,%Y ) 
  + INTERVAL 1 MONTH 
  - INTERVAL 1 DAY

Upvotes: 1

Suresh Kamrushi
Suresh Kamrushi

Reputation: 16076

1) I can use 1st as the days and can store along with month and year.

This option is better as if there is any calculation or sorting need to be done, it will be useful and handy.

2) Instead of using date I can use varchar datatype and can only store month and year in it.

3) I can store the two as two separate columns as integers. i.e. for month and year.

For above two option are fine as it will take less space as compare to date or timestamp. But you can do calucation or orderby with this. For calculation you need to convert it date by "str_to_date" function

I hope this will help you in some way.

Upvotes: 2

Konstantin
Konstantin

Reputation: 493

stored as UNIX. suddenly need to add something in the future, and no problem to get anything out of date

Upvotes: 1

Related Questions