php - Store full date or just an year in Database


I am working on a project where there are bunch of dates which I need to store in a database (MySQL).

The problem is, some dates are full, but some dates are just only year.

For example, the dates are like this:

  • 20 January 1980
  • 2011
  • 25 February 2012
  • 2008

I am using the functiondate() andstrtotime() function in PHP to convert the dates inY-m-d format. The full dates are converted successfully, but the dates with just only an year is converted to today's date which is wrong.

The questions are:

  • How to successfully determine which is a full date and which is just only an year in PHP and then apply thedate() andstrtotime() function to the full date only?
  • How to save these variations (full date and only year) in database? (if it is possible in a single column)
  • While fetching from the database, how to know whether the date is full or just an year and display it accordingly?


Confused. I will be doing operations on date such as sorting date-wise which will include both full and year only dates. Whether to:

  • Store year as 1990-00-00 and check for the zeroes while retrieving?
  • Store year as 1990-01-01 and another boolean field isYear?
  • Store year, month and day as integers in different columns?



In a database you store the objects you are dealing with. You can store names like 'John Smith' just as it is or you store first and last name separately (but where to put the middle name then? :-) It depends on your requirements. For one system it is necessary to filter and sort by last names, so last name must be a separate column. Another system may only be interested in the full name without the worries to bother which in "Wu Chang" is the first and which is the last name.

Same here. Usually one stores dates as a DATE or DATETIME column. But you are interested in the year where day and month can be unknown. So you store these separately. The year probably with a NOT NULL constraint, and day and month nullable.

I recommend separate integer columns for year month and day. Don't use a string with some format one must know and which may be broken every now and then.

EDIT: I just notice I haven't answered your PHP question: Well, when you get a string that consists of four digits it's a year, else you can either convert it to date, then it's a date, or you can't, then it's invalid data.




You can check if a given string only specifies a year by passing it to date_create_from_format:

$s = '2014';
if (date_create_from_format('Y', $s))
    $t = strtotime($s.'-01-01');
    $t = strtotime($s);

echo date('Y-m-d H:i:s', $t);



Make sure that you use zeroes on the unknown parts of the date. So insert '2014-00-00' instead of just 2014.




You can check if it's a number. If true then it's a year. So add "01/01/" before converting it to a date. It will be considered as being an event for january first. You need a separate field isYear (a boolean) to determine wether it is a year or a date.




To save the different date formats in the same column in the db you would have to save them as a String. This doesn't make sense if you want to do some operations on them but is fine if you just want to display the value. That being said, if you want to perform some operations on them I suggest having 2 columns in the db, one for the full year of type TIMESTAMP and 1 for the year as type YEAR. This way you also know which type you are loading by checking which column is not null and so solving your other question.

To answer your first question, you can check which is the full date and which is only the year by checking the string length. If the trimmed string is longer than 4 chars then it must be the full date. Still I would do error handling in case you have some incorrectly formatted data sent to you.

reading other replies, i wouldn't set it to 01/01/2014 for example, because if you have an actual date of 01/01/2014 you cannot determine if it was sent as full date or the year only. Alternatively you can follow this method and add a column of type BIT to represent a boolean stating if it was fullYear




If the format of the full dates is consistent then you can check if there is a full date by checking for spaces in the string.

Secondly, if you've got a date column in your table, you can save just the year value, and leave month and day as zeros.

if (strstr(trim($date), ' ') !== false) {
    // space was found, this is a full date
    $mysqlDate = /* ... strtotime, etc. */
} else {
    // space was not found, this is just a year date
    $mysqlDate = trim($date) . '-00-00';

When it comes to selecting, you can then filter year-only dates by checking for zeros in the date value.

Not a pretty solution, but the one that jumps to mind if you intend to store them all in the same column.

People are also looking for solutions to the problem: php - Extra fields in Wordpress user panel not shown


Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.