Tell me more ×
Drupal Answers is a question and answer site for Drupal developers and administrators. It's 100% free, no registration required.
up vote 2 down vote favorite

Very simply, I'm creating a date of birth field pragmatically like so:

  $form['dob'] = array(
    '#type' => 'date',
    '#required' => TRUE,
    '#title' => t('Date of Birth'),
    '#default_value' => $sponsorship->dob,
  );

The API says the return value is in the form:

array('year' => 2007, 'month' => 2, 'day'=> 15)

My question is simply, how should I store this in a database table? I originally set the table column to varchar. But I would like to know what is normal practice and most efficient for handling a date using the Form API for Drupal, both saving to the database and then displaying on a page.

share|improve this question

2 Answers

up vote 3 down vote

Drupal 7 Schema API Data types does not support the DATE datatype that you find in popular SQL dialects such as MySQL. My preferred solution is the same as the one you suggest (set the column to varchar and store it as a an ISO 8601 format date string: 2013-05-07 for May 7, 2013). It is human readable (useful when debugging) and it is the format used by the DATE format of most SQL dialects.

For date arithmetic, I use built-in PHP-functions such as gregoriantojd to convert to Julian dates to compute date differences, etc.

While it is possible to force Drupal to use the DATE type to your SQL dialect of choice - see this answer for details, a varcharis the simplest and most portable solution.

For displaying a date in Drupal, you may want to use the the Drupal format_date function (from common.inc), as this makes sure that dates are rendered consistent across the site, and adapt month names, etc. to the preferred language. Note that this function takes a Unix timestamp as its first argument, so you need to convert from the ISO 8601-format to a Unix timestamp in order to use this (and this won't work for dates fore 1970-01-01). Because of this limitation I instead use a small custom module with a slightly adapted copy of this core function that shares the format templates with the core function, but accepts an ISO 8601 date and datetime as valid input. I also supply my own custom format (you can set up this on the Home » Administration » Configuration » Regional and language » Date and time » Add Date type admin page) without any hours, minutes or seconds.

PS: Some people recommend using Unix timestamps (seconds from epoch starting 1970-01-01) for anything that has to to with time and date. However, I do not like to use Unix timestamps for dates where the resolution wanted is whole days. In addition to not being human readable, and having a rather limited timespan (making it inappropriate to store the date of birth for someone born before 1970), Unix timestamp dates based upon the local time tend to produce weird bugs when dates cross seasons because DST is being taken into account when you do not expect it. It may be considered strange that I depreciate the Unix timestamp when the format_date function wants this format - but I find that storing the dates in the database in the ISO 8601 format is so attractive that I put up this extra conversion step when calling the format_date function.

share|improve this answer
up vote -1 down vote

Unix timestamp is what I prefer.

share|improve this answer
date() and mktime() function can help you conerting among each timestamp to date format. – arpitr May 7 at 18:43
3  
This does not answer the question. Please expand your answer to say why this is your preference, and to show the OP how to accomplish it within the parameters of his question. – Triskelion May 7 at 19:04
Got a lesson thanks @Gisle :) – arpitr May 7 at 20:31

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.