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
Comment | File | Size | Author |
---|---|---|---|
#29 | views-476774-29.patch | 13.81 KB | bendiy |
#30 | views-476774-30.patch | 15.66 KB | bendiy |
#18 | views_handler_field_date.inc_.patch | 5.15 KB | bendiy |
#11 | views_handler_field_datetime.inc_.txt | 864 bytes | jonathan1055 |
_views_handler.convert_datetime.patch.txt | 742 bytes | jonathan1055 |
Comments
Comment #1
NancyDrusubscribing
Comment #2
markDrupal CreditAttribution: markDrupal commentedThis fixes my problem!
Thanks, I can confirm it works with displaying DATETIME fields and sorting by DATETIME fields
Comment #3
NancyDruI concur.
Comment #4
KarenS CreditAttribution: KarenS commentedNot 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.
Comment #5
NancyDruMany 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.
Comment #6
KarenS CreditAttribution: KarenS commentedI 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.
Comment #7
NancyDruTimezones 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.
Comment #8
jonathan1055 CreditAttribution: jonathan1055 commentedHi 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
Comment #9
merlinofchaos CreditAttribution: merlinofchaos commentedA 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.
Comment #10
NancyDruThis 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.
Comment #11
jonathan1055 CreditAttribution: jonathan1055 commentedOK 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:
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:
Please let me know if I am on the rights lines here, or whether this is still insufficient to be a proper solution.
Jonathan
Comment #12
jonathan1055 CreditAttribution: jonathan1055 commentedPlease 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
Comment #13
NancyDruI get a message saying those two fields don't exist.
Comment #14
markDrupal CreditAttribution: markDrupal commentedI 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.
Comment #15
DamienMcKennaAm also interested to see where this goes..
Comment #16
webchickHm. 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().
Comment #17
d0t101101 CreditAttribution: d0t101101 commentedThanks,
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:
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.
.
Comment #18
bendiy CreditAttribution: bendiy commentedI 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.
Comment #19
Azol CreditAttribution: Azol commentedSubscribing
Comment #20
pimousse98 CreditAttribution: pimousse98 commentedsubscribing
Comment #21
spydmobile CreditAttribution: spydmobile commentedsubscribing
Comment #22
merlinofchaos CreditAttribution: merlinofchaos commentedOk, 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().
Comment #23
bird-cage CreditAttribution: bird-cage commentedSubscribing: 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.
Comment #24
achtonsubscribing
Comment #25
indigoblue CreditAttribution: indigoblue commentedMy 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.
Comment #26
3dloco CreditAttribution: 3dloco commented+1
Comment #27
bendiy CreditAttribution: bendiy commentedI'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".
Comment #28
Todd Young CreditAttribution: Todd Young commentedJust 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...
Comment #29
bendiy CreditAttribution: bendiy commentedI 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:
TODO:
Timezone offsetting features need to be determined and finalized. Preferably in the parent class if possible.See patch #30 below.Please let me know if this looks like a good approach.
Thanks!
Comment #30
bendiy CreditAttribution: bendiy commentedHere 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().
Comment #31
Cyberwolf CreditAttribution: Cyberwolf commentedSubscribing.
Comment #32
KarenS CreditAttribution: KarenS commentedFor 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.
Comment #33
rconstantine CreditAttribution: rconstantine commentedI 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:
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.
Comment #34
bendiy CreditAttribution: bendiy commented@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.
Comment #35
NancyDruD7 does not support PHP 4. However, PHP 5.3 is not required for D7, so that could be a big issue.
Comment #36
merlinofchaos CreditAttribution: merlinofchaos commentedWe'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.
Comment #37
bendiy CreditAttribution: bendiy commentedComment #38
tim.plunkettSubscribe
Comment #39
zilverdistel CreditAttribution: zilverdistel commentedsubscribing
Comment #40
lsolesen CreditAttribution: lsolesen commented+1
Comment #41
Mark TrappSubscribe
Comment #42
thomsol CreditAttribution: thomsol commentedHas 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.
Comment #43
merlinofchaos CreditAttribution: merlinofchaos commentedI 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.
Comment #44
bartl CreditAttribution: bartl commentedI 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:
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.
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 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.
Comment #45
ParisLiakos CreditAttribution: ParisLiakos commentedSo, shouldnt this be moved to date's issue queue?
i agree that its place should be there
Comment #46
KarenS CreditAttribution: KarenS commentedYou 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.
Comment #47
Liam MorlandTagging
Comment #48
bvanmeurs CreditAttribution: bvanmeurs commentedThe 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.
Comment #49
bvanmeurs CreditAttribution: bvanmeurs commentedAnother 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.
Comment #50
faboulaws CreditAttribution: faboulaws commentedComment #51
Anybody#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.
Comment #52
asauterChicago CreditAttribution: asauterChicago commentedThanks 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!
Comment #53
kenorb CreditAttribution: kenorb commentedSee: #2178287: allow views_handler_field_date to work with SQL datetime format