When a field is defined in the database as 'datetime' (not unix timestamp) the existing views date handler does not format the date correctly. My datetime values are strings with the year at the start, so 2009 is used as the input number to the date formats and hence I get formatted dates of 1st January 1970 00:33:29 (because 2009 seconds = 33 minutes 29 seconds).

I think a simple solution is to detect whether the raw date value is an integer, and if not then it will be a datetime, so convert it
to the expected number using strToTime() as follows:

    $value = $values->{$this->field_alias}; // this line does not change, but included it here for reference.
 
    // If the value is not a simple number then the database field is (most likely) defined as a datetime.
    // Convert this back to a number to allow correct formatting.
    if (!is_numeric($value)) {
      $value = strToTime($value);
    }

The attached patch to views_handler_field_date.inc does this. I am surprised no one has encountered this problem yet, or maybe datetime fields are not so widely used as unix timestamp fields.

Jonathan

Support from Acquia helps fund testing for Drupal Acquia logo

Comments

NancyDru’s picture

subscribing

markDrupal’s picture

This fixes my problem!
Thanks, I can confirm it works with displaying DATETIME fields and sorting by DATETIME fields

NancyDru’s picture

Status: Needs review » Reviewed & tested by the community

I concur.

KarenS’s picture

Status: Reviewed & tested by the community » Needs work

Not sure about this. strtotime() creates implicit timezone conversions that may or may not produce the right results. The fact that it happened to work right for a couple people does not mean it will work correctly everywhere. Someone needs to specifically test that the date produced is correct in various scenarios -- a user has a timezone different than the site timezone, PHP or the server is using a timezone different than the site timezone, etc. Depending on where the datetime field is coming from, it may be that some other timezone should be used or that the strtotime timezone should be set to UTC.

Where are these datetime fields coming from? Core does not create any.

NancyDru’s picture

Many contribs do, and many more will begin thinking about it as 2037 approaches (remember Y2K?) and Unix time wraps back to 0.

In my case, these are coming from Web Links. Unfortunately, WL only cares about server time.

KarenS’s picture

I think that the right solution for this is not to use the default Views formatting, which is based on the unix timestamps that core uses. Instead the contrib modules that provide these date fields should probably create their own handlers to format the dates correctly, incorporating timezones in in an appropriate way. I can't see any easy way to create generic Views handling for this that will 'know' how to handle the timezone for these custom fields, nor do I know any easy way to force strtotime() to do a proper timezone conversion for timezones other than UTC or PHP's default timezone.

NancyDru’s picture

Timezones aside, IMHO, Views (and/or DateAPI) should at least be able to accept DATETIME values, whether with a different handler or as this patch does. DATETIME is not uncommon in the real world, and the end of usefulness of Unix time stamps is actually already here (think of a 30 year mortgage calculation). Let's not wait for Dec. 31, 2036 to realize this.

jonathan1055’s picture

Hi KarenS,
Thanks for your response but I don't think that forcing contrib module developers to create their own datetime handlers is the right way to do it. At best this would lead to duplicated code and effort spent, and at worst would create inconsistencies, inaccuracies and loss of functionality. It is not a reasonable overhead to expect module developers to have an indepth knowledge of how views works, they should just be able to say 'this field is a date - views, do your stuff with it' like they can now with unix timestamps. I am sure we can work out how to handle timezones, and doing this centrally is by far the better way, IMO.

As you know, the format_date() function which views uses already caters for putting the date into the users timezone or site timezone. So a datetime value stored in a node table only has to replicate the same meaning that is stored by a unix timestamp, ie no offset. The module developer would be responsible for converting this, if necessary, before writing the value, but this is no more of an overhead than now.

I may have missed a fundamental point, so let me know if I am completely off track here!

Jonathan

merlinofchaos’s picture

A proper datetime handler maybe. This is not a proper datetime handler. You're taking a handler that's meant to handle unix timestamps and making it work for one case. This is insufficient.

NancyDru’s picture

This will probably give Earl a heart attack, but I sort of agree that a proper Datetime handler is probably a better solution in the long run because the current Unix timestamp handler is going to be changed sooner or later as 2037 approaches.

@KarenS: Does the DateAPI handle Sql Datetime values? Perhaps the heart of this handler would be best in that API.

jonathan1055’s picture

OK I understand that attempting to detect the datetime value within the existing handler is unacceptable, so I've created a specific datetime handler which extends the existing one. The new file is views_handler_field_datetime.inc, and the conversion is done in function render() as follows:

// $Id$

/**
 * @file
 * Provide extension of date handler
 */

/**
 * A handler to display dates which are DATETIME not unix TIMESTAMP.
 *
 * @ingroup views_field_handlers
 */
class views_handler_field_datetime extends views_handler_field_date {

  function render($values) {

    // Convert the stored datetime text string into a unix timestamp, as this is what the
    // regular unix timestamp formatter is expecting. This function will be called for each datetime
    // field in the view, so if a field is repeated it must only be converted once.
    static $converted = array();
    if (!$converted[$this->field_alias]) {
      $values->{$this->field_alias} = strToTime($values->{$this->field_alias});
      $converted[$this->field_alias] = TRUE;
    }

    // Render the date using the normal date handler
    return parent::render($values);
  }

}

If there is a better way to do the conversion so that we don't have to check that it's only done once, then that would be good.

Am I correct in my comments in #8 that timezones are catered for correctly, providing that modules which store dates in datetime fields use the same input conversions as is done for normal dates?

For weblinks users - to test this, create the file above in the weblinks directory, change 'views_handler_field_date' to 'views_handler_field_datetime' for the two date fields in file weblinks.views.inc and add an extra line in weblinks.module weblinks_views_handlers() as follows:

function weblinks_views_handlers() {
  return array(
    'info' => array(
      'path' => drupal_get_path('module', 'weblinks'),
      ),
    'handlers' => array(
      'views_handler_field_weblinks' => array('parent' => 'views_handler_field'),
      'views_handler_field_datetime' => array('parent' => 'views_handler_field_date'),  // this is the new line to add
      ),
    );
}

Please let me know if I am on the rights lines here, or whether this is still insufficient to be a proper solution.

Jonathan

jonathan1055’s picture

Status: Needs work » Needs review

Please excuse the bump, but it is 6 weeks since I posted the above solution. Are there any problems with it? I accept that we are all busy with many tasks, so I understand if its just the case that no-one has got round to looking at it yet. But I would like to know if this will ultimately be accepted into views so that I can plan whether to use it in other modules or not.

Thanks,

Jonathan

NancyDru’s picture

I get a message saying those two fields don't exist.

markDrupal’s picture

I put your handler in the views module directory, views/handlers/views_handler_field_datetime.inc Then cleared the cache, and changed my custom module to use the handler.

I got "Error: ... Handler doesn't exist"

Are files in the handlers directory automatically included? I can't find where in the views module where the default handlers are included. I can do it in external modules like in #11.

Can you make a patch against the views repository? You'll want to do this anyways as the module maintainers may not take the request seriously unless a real patch is included.

So, so far this isn't working for me, it looks like it kills some of the date_api functionality too, but it might just be because of the handler file is not included.

DamienMcKenna’s picture

Am also interested to see where this goes..

webchick’s picture

Status: Needs review » Needs work

Hm. I'm pretty sure the patch in #11 has the same problem that KarenS pointed out in #4 about the initial patch; it's blindly doing a strtotime() without factoring in the timezone information. You probably need something a bit more robust that can handle all of the various permutations of hook_date_api_fields().

d0t101101’s picture

Thanks,

I'm relatively new to coding views handlers and the date_api, and found your handler code helpful as a start for my custom module.

To anyone having problems using this handler code in #11, I was able to get it to work by:

1- Placing views_handler_field_datetime.inc in my custom modules directory
2- Copying and rewriting the weblinks_views_handlers() code in #11 for my modules name

Since my module is trying to render datetime's in a calendar view using date_api, the check for 'if (!$converted[$this->field_alias])' caused only the first field in the calendar to be rendered correctly. I commented out this if statement, and it ALMOST works as needed. My changes are below:

class views_handler_field_datetime extends views_handler_field_date {

  function render($values) {

    // Convert the stored datetime text string into a unix timestamp, as this is what the
    // regular unix timestamp formatter is expecting. This function will be called for each datetime
    // field in the view, so if a field is repeated it must only be converted once.
    static $converted = array();
    //if (!$converted[$this->field_alias]) {
      $values->{$this->field_alias} = strToTime($values->{$this->field_alias});
      $converted[$this->field_alias] = TRUE;
    //}

    // Render the date using the normal date handler
    return parent::render($values);
  }

}

Next, I need to write some custom date handling inside of this views handler to extract my UTC datetime's stored in the database and convert them to the users local timezone for display. This I expect will be more involving. If anyone can point me to some similar views handler code it would be much appreciated.

.

bendiy’s picture

Version: 6.x-2.5 » 6.x-2.8
Category: bug » feature
Status: Needs work » Needs review
Issue tags: +PostgreSQL, +date, +datetime, +timestamp
FileSize
5.15 KB

I don't mean to hijack this thread, but there's been no activity for 6 months and I need this functionality.

I have created a patch to views_handler_field_date.inc that adds support for 'date', 'datetime', 'datetime_with_timezone', 'ical', 'ical_date' and 'iso' date formats. This patch allows you to specify that your date is in a non-timestamp format and then it checks that the supplied value meets that formats pattern. It will then use strtotime() to convert that format to a timestamp.

There is also a custom format. This option will not check to see if the value matches a pattern, but simply passed to strtotime() for conversion. This should work as long as you know that you date value format will be successfully converted by strtotime().

I have also added support for a data source timezone offset. This allows you to specify if you source date is in a different timezone for your web server and then offset that value by a specific number of hours.

Once the date is converted, it then acts just like a normal Views date timestamp value and allows you to output it in the standard Views date formats as well as offset it for the user's timezone.

Please review this code and test it. I'm using PostgreSQL and it's working for my date formats.

Azol’s picture

Subscribing

pimousse98’s picture

subscribing

spydmobile’s picture

subscribing

merlinofchaos’s picture

Status: Needs review » Needs work

Ok, my opinion is that this should be its own handler, and not conflated with the existing date handler. The existing date handler is specifically geared toward timestamps. Trying to combine them just makes things ugly.

At *best* we should have a parent system where there is a core date class that handles shared functionality, and then specific children to handle dates of different types. But they definitely should not be all in one do-everything class.

Also, the patch I just glanced at has the *user* specify the data type, and that is never correct. The data type is specified by the hook_views_data().

bird-cage’s picture

Subscribing: My problem is with dates of birth which are often prior to 01.01.1970 hence I need to use a datetime field (negative timestamps don't work). I will have a go at writing my first views handler from what is above to solve the problem for my module. If I think it could be of any use to somebody else, I'll post my results here.

achton’s picture

subscribing

indigoblue’s picture

My vote is for a seperate default views handler for datetime. To get #11 to work for me, I had to remove the checks for repeat conversions. It worked for me.

3dloco’s picture

+1

bendiy’s picture

I've run into a small timezone issue with my patch above, #18. I found that I need to add a "0" to the format_date()function for my custom format. See the comment at the bottom here: http://api.drupal.org/api/function/format_date

Line 155 for me.

case 'custom':
return format_date($value, $format, $custom_format, 0);

I agree with merlinofchaos that this is a hack and there should be a parent system to handle shared date functions and then at least two child classes, timestamp and datetime, that handle format specific functionality.

The name of the current handler, "views_handler_field_date", should really be "views_handler_field_timestamp".

I think it would make the most since to make the existing handler, "views_handler_field_date", the parent class that has at least two child classes, "views_handler_field_datetime" and "views_handler_field_timestamp", that handle the format specific features. Move most of the current handler's code to "views_handler_field_timestamp".

Todd Young’s picture

Just a little side-nugget, dunno if it's been addressed; but those who would be working with datetime fields will also likely have dates like '0000-00-00 00:00:00' instead of nulls, which teh suggestion(s) above do weird things withm, such as displaying dates as '-0001-11-30 01:00' and the like...

bendiy’s picture

FileSize
13.81 KB

I spent some time today working on a real solution to this issue. I wanted to post my progress to get some feedback before finalizing the patch.

Changes made:

  1. 'views_handler_field_date.inc' is now the parent class for two new handlers, 'views_handler_field_datetime.inc' and 'views_handler_field_timestamp.inc'. There isn't any shared functionality yet, but it is at least structured this way for future changes. Timezone offsets may work well in the parent class.
  2. I have completely changed 'views_handler_field_date.inc' to be an actual 'date' data type handler because that is what it is named as. This is a BIG change because all modules that use this handler in their hook_views_data() function will need to edit them to use the new 'views_handler_field_timestamp.inc'. I believe this semantic change needs to be made at some point, so why not now. There might be a way to build in some backwards compatibility by checking the modules Views API version or adding a $this->definition['date_type'] = 'date' or $this->definition['date_type'] = 'timestamp' to the hook_views_data() array. You could then process the fields using the old timestamp style handler if the check failed. See below:
    <?php
      $data['my_table']['date_field'] = array(
        'title' => t('Date Field'),
        'help' => t('Date Field'),
        'field' => array(
          'handler' => 'views_handler_field_date',
          'click sortable' => TRUE,
          'date_type' => 'date', // If this is not set, we assume they are still using timestamp.
          'date_format' => 'Y-m-d', // This could be useful for something...
        ),
        'filter' => array(
          'handler' => 'views_handler_filter_datetime',
        ),
      'argument' => array(
          'handler' => 'views_handler_argument_date',
        ),
        'sort' => array(
          'handler' => 'views_handler_sort_date',
        ),
      );
    ?>
    
  3. I have added 'views_handler_field_datetime.inc' which functions similar to the new 'views_handler_field_date.inc', but includes the time when rendering the field by default and uses the offsets like you could with the old timestamp feature.
  4. I have added 'views_handler_field_timestamp.inc' which is just a copy of the old 'views_handler_field_date.inc'.
  5. Added references to these new handlers in 'handlers.inc'

TODO:

  • More handlers will be added for 'datetime_with_timezone', 'ical', 'ical_date' and 'iso' date formats.
  • A patch will also need to be created to change all of the default modules that are supported by Views to change the hook_views_data() to 'handler' => 'views_handler_field_timestamp'.
  • Timezone offsetting features need to be determined and finalized. Preferably in the parent class if possible. See patch #30 below.
  • Additional argument, filter, and sort handlers may need to be created for these date formats.

Please let me know if this looks like a good approach.

Thanks!

bendiy’s picture

Title: Allow date handler to cope with 'datetime' fields » Allow date handler to cope with 'date', 'datetime', 'datetime_with_timezone', 'ical', 'ical_date' and 'iso' formats
Version: 6.x-2.8 » 6.x-3.x-dev
Status: Needs work » Needs review
FileSize
15.66 KB

Here is an updated version of the patch. This version adds support for user timezone offsets for 'date' and 'datetime' fields. The old, 'timestamp', fields were already offset by format_date().

Cyberwolf’s picture

Subscribing.

KarenS’s picture

For whatever it's worth, this patch will not work in any version of php earlier than php5.2. And in Drupal 6 we are officially still supporting php4. So this can't be used unless Views wants to make php5.2 a requirement.

rconstantine’s picture

I tried to use the patch in #30 but found it to be incomplete. So I poked around and think I have things working.

First of all, the filter, sort, and argument handlers listed in the example in #29 don't exist. I assume the author intended to write them. I haven't played with sort or arguments yet, but my relevant views_data looks like this:

'dob' => array('title' => t("DOB"), 'help' => t("The patient's date of birth."),
          'field' => array('handler' => 'views_handler_field_datetime', 'click sortable' => TRUE),
          'argument' => array('handler' => 'date_api_argument_handler'),
          'filter' => array('handler' => 'date_api_filter_handler', 'base' => 'MY BASE TABLE NAME HERE'),
          'sort' => array('handler' => 'views_handler_sort')),

Well, the date_api_filter_handler wasn't working for me, so I modified a couple of files.

First, in date_api_fields.inc, I added this: elseif ($handler_name == 'views_handler_field_date' || $handler_name == 'views_handler_field_datetime' || $handler_name == 'views_handler_field_timestamp') {

Only the first item in the elseif had been there previously.

Second, (and I'm not sure why this didn't work for me, but I was getting zero results) I had to change date_api_sql.inc thus: $field = "STR_TO_DATE($field, '%Y-%m-%%d %H:%i:%%s')"; which is found in the DATE_ISO case of the function sql_field. Note that since %d and %s are special in Drupal's db_query processing that the double %% is required.

The sorting seems to work as expected, at least when the display format is Y-m-d, which is what I need. A more robust sorting handler might be nice.

I am not sure I have a need for the argument handler, so I'm not sure I'll get around to testing it. I haven't even opened it up to see if it also requires the 'base' array element as the filter handler did. That one had me going around in circles for quite a while.

Note, I didn't check whether the latest version of the date module has any of my changes already in it. I need to go through and update a lot of modules, but haven't done so.

I agree that more work is required to get this where it needs to be (esp since PHP4 support is required), but for those that need this now, it's a good start.

bendiy’s picture

@rconstantine thanks for the feedback. I haven't had time to finish this patch completely, but what is there should be working.

This may have to be a Views 3 for Drupal 7 patch to get around the PHP4 issue. However, PHP didn't add some of the more robust date handler features until the last few releases. I think this may need PHP 5.3 to get all the features working. It could always be re-written to work in over versions, but that will take a lot more code.

NancyDru’s picture

D7 does not support PHP 4. However, PHP 5.3 is not required for D7, so that could be a big issue.

merlinofchaos’s picture

We've toyed with requiring PHP 5.2 for Views 3 but I don't think we've ever formally decided to do it. So this may need to be restricted to Drupal 7.

A requirement of PHP 5.3 is not going to be workable. Large swaths of Drupal currently don't work well with PHP 5.3 yet. Though in D7 that's probably no longer true.

bendiy’s picture

Version: 6.x-3.x-dev » 7.x-3.x-dev
Status: Needs review » Needs work
tim.plunkett’s picture

Subscribe

zilverdistel’s picture

subscribing

lsolesen’s picture

+1

Mark Trapp’s picture

Subscribe

thomsol’s picture

Has there been any progress on this issue? I have virtually the identical problem rconstantine identified in post #33 (even including field name: patient_dob). I'm going to try and implement his solution, however I'd strongly prefer this to be a supported solution. Views is an invaluable and much appreciated tool, and it would be great to see it support native MySQL dates.

merlinofchaos’s picture

Status: Needs work » Closed (won't fix)

I think any support for this would need to be in date_api module. native database dates are much, much more complex than core's timestamp based dates and I'm not prepared to support them.

bartl’s picture

I found this thread through Google. I'm replying here mainly to show that interest in this problem is still not dead.

I have seen that at least some people have been taking the same route on thinking about a solution, as I had. BTW the status of "closed (won't fix)" for the thread is not really helping, but I have come to think of it as typical for the mindset in the Drupal community: acting like a problem just doesn't exist (I hate that attitude).

Anyway, here are a few of my thoughts:

  • The standard behavior for Drupal for storing time and date in the database as Unix timestamps is short-sighted. As stated, a date of birth can not be stored in the database as a Unix timestamp, because they're often from before 1970. Besides, it is an abstraction, a value that is independent of time zones: my date of birth will always be the same whether I'm in Vietnam or in LA. In that way, storing it as a timestamp does not make sense, even when it is possible.

    And what about time? Mysql has direct support for a time value, and again, storing a time (every day at 10AM, for example) as a Unix timestamp, makes no sense at all.

    Furthermore: the data in the database may have other uses than just Drupal, and a Unix timestamp is a very user-unfriendly format for people who have to work with that data directly.

  • Renaming the standard Views handler for Date is not acceptable. We must think of backward compatibility, a date handler will always be based on (Drupal's idea of) timestamps. That means we need a different name for support of a field of type "date" in the database. I propose something like "dbdate". Anything but "date".
  • However it goes, I still want to make use of Date's support for entering dates in forms, and formatting in Views. So basing the implementation on (or inherit from) Date makes sense.

    That does not mean I want to go through PHP's built in functions for manipulating dates... Actually, databases (including Mysql and Postgres) do have proper built in functions both for formatting dates as strings, and for extracting fields (like year, month and day) from dates. Perhaps we could leverage that database support, instead of relying entirely on PHP?

  • I can accept that this kind of support will not be part of the Date module. However, forcing module authors to write their own handler is not an acceptable "solution" either, as writing Views handlers is anything but trivial, and as a lot of people need the exact same functionality for their own module, requiring each to redo the same work over and over again is duplicate effort, and anything but productive.

    I would propose either an add-on module, on top of Date, or at least a template or cookbook example for a handler that people can customize for their own needs in their own module. It depends on how specific module writer's needs are. I have no idea which is the best approach.

ParisLiakos’s picture

Project: Views (for Drupal 7) » Date
Version: 7.x-3.x-dev » 7.x-2.x-dev
Status: Closed (won't fix) » Needs work

So, shouldnt this be moved to date's issue queue?
i agree that its place should be there

KarenS’s picture

Status: Needs work » Closed (won't fix)

You can't take a Views patch and drop it in the Date issue queue and expect me to do something with it. Date already has a ton of its own features and all the previous conversations about what is in Views are irrelevant to it. So are questions about what should be in core.

If you want Date to do something, start by looking at what is already there. Then if you want to propose a Date feature, explain how it would fit into what we have now. Most of the conversation in this thread is just confusing when trying to figure out what it means to Date, so please start a fresh issue that focuses on what, exactly, you want Date to do.

Liam Morland’s picture

Tagging

bvanmeurs’s picture

The following code works on MySQL only but could probably quickly be rewritten to be supported by other databases. Maybe it's useful to someone reading this thread.

Imho this functionality could be valuable in the Views module, so I will create a post there.

/**
 * A handler to display dates that are DATETIME instead of unix timestamp.
 *
 * @ingroup views_field_handlers
 */
class views_handler_field_datetime extends views_handler_field_date {

  function get_value($values, $field = NULL) {
    $value = parent::get_value($values, $field);
    if (!empty($value)) {
      // Convert database datetime value to timestamp, so that the date handler
      // understands it.
      $datetime = new DateTime($value);
      $value = $datetime->getTimestamp();
    }
    return $value;
  }

}

/**
 * A handler to filter dates that are DATETIME instead of unix timestamp.
 *
 * @ingroup views_filter_handlers
 */
class views_handler_filter_datetime extends views_handler_filter_date {

  function op_between($field) {
    parent::op_between("UNIX_TIMESTAMP(" . $field . ")");
  }

  function op_simple($field) {
    parent::op_simple("UNIX_TIMESTAMP(" . $field . ")");
  }
}

/**
 * A handler to sort dates that are DATETIME instead of unix timestamp.
 *
 * @ingroup views_sort_handlers
 */
class views_handler_sort_datetime extends views_handler_sort_date {

  /**
   * Called to add the sort to a query.
   */
  function query() {
    $this->ensure_my_table();

    switch ($this->options['granularity']) {
      case 'second':
      default:
        $this->query->add_orderby($this->table_alias, $this->real_field, $this->options['order']);
        return;
      case 'minute':
        $formula = "DATE_FORMAT({$this->table_alias}.{$this->real_field}, '%Y%m%d%H%i')";
        break;
      case 'hour':
        $formula = "DATE_FORMAT({$this->table_alias}.{$this->real_field}, '%Y%m%d%H')";
        break;
      case 'day':
        $formula = "DATE_FORMAT({$this->table_alias}.{$this->real_field}, '%Y%m%d')";
        break;
      case 'month':
        $formula = "DATE_FORMAT({$this->table_alias}.{$this->real_field}, '%Y%m')";
        break;
      case 'year':
        $formula = "DATE_FORMAT({$this->table_alias}.{$this->real_field}, '%Y')";
        break;
    }

    // Add the field.
    $this->query->add_orderby(NULL, $formula, $this->options['order'], $this->table_alias . '_' . $this->field . '_' . $this->options['granularity']);
  }

}
bvanmeurs’s picture

Another addition: have a look at the following handlers:
date_views_filter_handler_simple
date_views_argument_handler_simple

They are part of the date module and work just fine when you have a database field with type datetime.

faboulaws’s picture

Anybody’s picture

#48 works absolutely great! What about a separate or sandbox module to make this usable, if it should not be added into "date" project?

I think that would make a lot of sense to document this best practice.

asauterChicago’s picture

Issue summary: View changes

Thanks for posting this, number 48 with the additional date_views_filter_handler_simple, date_views_argument_handler_simple works great for me as well. I just created a seperate includes/views/handlers.inc in my module and put this code in there. Now my datetime fields all render correctly.

BTW, it looks like Data also has made their own implementation of this exact same thing: https://drupal.org/node/2178287, if anyone doesn't want to have to code their own module. I haven't tried it, because all I needed was this one specific handler so I don't know how it works, but hopefully this might help someone.

Anyway, thank you!