Date field support

It worked with a previous version of the Date module. Restructuring in that module made a big rewrite necessary. A simple hack won't do anymore. I estimate this at 100-200 LoC.
Patches or sponsoring welcome.

I would appreciate this functionality also
let me know how I can help

Nick, coordinate with Ranko and wipeout_dude to get this sponsored.

Ok I will try thanks Do you think they are in the irc?

Yes, I too would like to see this module support Date module fields. I would be willing to sponsor what I can.

+1

+1

+1, how does sponsorship work here ? we might be able to here as well.

I can contribute 50$ as well. We need 4 more.

You are right!

I'll chip in $200. I need this also.

I can chip in another $50 towards this development work - I need this feature too

Thanks
Gregg

I've played a bit with this problem and I think I got the basic functionality working (at least for the use cases I've tested).
I have basically just extended the 'views_handler_field_field' handler that is normally used for date field and changed the bits that apply to the 'format_date_sql' functionlity. Otherwise it should fallback to the default 'views_handler_field_field' handler behavior.

Thanks Tadej. Looks good, but I won't have time to really test this for at least a few weeks. Maybe we can get some community opinions on it.

Patch from #17 works great for me.

Ready to test, but need use cases.

I didn't have a bunch of time to troubleshoot, but applying the patch broke my date handlers.

The handler for this item is broken or missing and cannot be used. If a module provided the handler and was disabled, re-enabling the module may restore it. Otherwise, you should probably delete this item.

 Date/Time         Date (date)                                                  Module  Enabled        7.x-2.6       
 Date/Time         Date All Day (date_all_day)                                  Module  Not installed  7.x-2.6       
 Date/Time         Date API (date_api)                                          Module  Enabled        7.x-2.6       
 Date/Time         Date Context (date_context)                                  Module  Not installed  7.x-2.6       
 Date/Time         Date Migration (date_migrate)                                Module  Not installed  7.x-2.6       
 Date/Time         Date Popup (date_popup)                                      Module  Enabled        7.x-2.6       
 Date/Time         Date Repeat API (date_repeat)                                Module  Not installed  7.x-2.6       
 Date/Time         Date Repeat Field (date_repeat_field)                        Module  Not installed  7.x-2.6       
 Date/Time         Date Tools (date_tools)                                      Module  Not installed  7.x-2.6       
 Date/Time         Date Views (date_views)                                      Module  Enabled        7.x-2.6       
 Views             Views Date Format SQL (views_date_format_sql)                Module  Enabled        7.x-3.1 

Ok, part of my problem in #20 was the patch didn't apply cleanly. I manually applied the patch and things started to work.

Performance Note:
I'm using the same date field in different formats to build up a file path in my public files folder to print a URL to PDF documents i.e. sites/defaults/files/reports/2013/06/updates.pdf, sites/defaults/files/reports/2013/06/research.pdf, etc. The date is the same date field. So, this results in multiple fields being returned from the DB. Previously this would only be a single field with render() formatting thrown on top of it. This doesn't really matter to me, but it might have an effect on others.

Marking RTBC.

Can someone pls link to a patched module zip. The #17 patch didn't work for me and I want to help testing.

You have to apply to the "master" branch, which I suppose is the -dev version. Anyway, here's the patched version.

Patch in #17 applied to master branch is mostly working, but has an issue with timezone handling: If I create a node with a date field that has Y-m-d granularity, enter 06/01/2013, it gets created as 2013-06-01 00:00:00.

When an aggregation-enabled views field with "Use SQL to format date" turned on, this field value 2013-06-01 00:00:00 gets rendered as an aggregate grouping for 2013-05 or "May 2013".

I remember coming across this bug in a handful of other modules (Entity tokens, Feeds) and it has to do with the timezone handling and handling of the hour/minute/seconds on the date value.

Once this headache is sorted, I am happy using this patch. Thank you!

patch worked for me. Although my dates on the x axis are in alphabetical order, rather than sequential from oldest to newest.

@Exploratus: try adding a sort criteria for that date field. If it breaks your aggregation, change the sort criteria's aggregation settings to "Minimum" and see if that helps.

I temporarily fixed my timezone issue by setting the site's default timezone to UTC (not ideal, but works!)

Please advise on how to improve timezone handling in Views Date Format SQL

Thanks for the awesome module.

Thanks @lunk_rat. It worked with minimum in the sort.

I was trying to use this to format dates for google charts tools module to prevent timestamp of 00:00:00 appearing - However I wanted the input to be pasted on a custom date field.

I found a work around that worked for me, Views Raw SQL
By formatting the at the SQL layer, I was able to get exactly what I needed.

CAST(field_data_field_kpi_date.field_kpi_date_value AS DATE)
in my example.

Also given the amount of documentation for SQL (and in particular MySQL) date formatting, hopefully an easy for those who don't use SQL.

my first post on drupal

DMex:

Thanks for the pointer on Views Raw SQL module.

AND welcome to drupal.org!

RTBC #17

FYI #23 totally fails with sqlserver using sqldrv-- the date field is simply missing from the query:

SELECT TOP(10) node.[nid] AS [nid],  AS node_created
FROM 
{node} node
LEFT JOIN {field_data_field_date} field_data_field_date ON node.nid = field_data_field_date.entity_id AND (field_data_field_date.entity_type = 'node' AND field_data_field_date.deleted = '0')
WHERE ( (( ([node].[status] = '1') AND ([node].[type] IN  ('high_impact_outage')) AND ('' + CAST(DATEPART(year, DATEADD(second, -18000, DATEADD(s, field_data_field_date.field_date_value2, '19700101 00:00:00:000'))) AS nvarchar) + '' = '2014') )) )

Note how views/date handle year granularity in an exposed filter for a date field-- it's correctly translated into sqlsrv syntax. I'm trying to trace this down, but any ideas of where to look would be greatly appreciated.

I am trying to make a View with either Start Date or End Date (as used in the Rooms module), but the application of the file in nr 23 doesnt change anything for me: the checkbox does not appear in the configuration box of these fields.
And as to the talk here of the master branch and dev versions.... there currently is only one version of this module afaik.

#17 works good for me, thanks a lot!

Tested better, and this patch doesn't work with Date (Unix timestamp) field type.
I changed #17 patch to fix it.

For me #35 patch works good!

Ok I am a total idiot but I don't get how to apply this

[root@tmp-dev views_date_format_sql]# git apply views_date_format_sql-date_field_support-1830436-35.patch
error: patch failed: views_date_format_sql.info:6
error: views_date_format_sql.info: patch does not apply

@rkent_87: Try to use patch -p 1 < /full/path/to/file/views_date_format_sql-date_field_support-1830436-35.patch

Hello when a delta of multiple date field is inserted in a view and I use views_date_format_sql (version included in comment #23) the sql query fails because of a field "node. AS node_" in views sql query. Do you have any thoughts?
Thank you.

Adding a new condition to check for date module fields, fixed the issue for me.

Thanks,
Vinoth

If the patch needs a patch, someone needs to reroll so we can retest I guess. (#35 and #40 combined)

One patch for a user and second for a developer. The problem in the file .info. The failed to apply the patch due to a timestamp and version of the module.

Finally successful on manually applying the patch. No more Broken Handlers for fields that I used.
It seems that i need to Flush all Caches first after applying the patch....

I tested with the following set-up;
Built 2 Date formats, Monthly Format (y-M) and Daily Format (Y-M-d)
I'll be building Views using Table, Chart (charts_highcharts), and Table with Aggregation options (Views Aggregate Plus)
3 fields Content: Post Date, Content: Custom_Date(

It works perfectly if I use it for Table with Aggregation options format. for the Daily and Monthly Format. (using a custom Date Format, or a Regional Settings )

I am really having trouble using Table and Charts and it doesnt work properly, no aggregation. (I just want to make a line graph of created nodes per Day :) )

Here's what the SQL query generates.

SELECT node.created AS node_created_1, DATE_FORMAT((DATE_ADD('19700101', INTERVAL node.created SECOND) + INTERVAL 28800 SECOND), '%Y-%b-%d') AS node_created, COUNT(node.nid) AS nid
FROM
{node} node
WHERE (( (node.status = '1') AND (node.created > 1432409672) ))
GROUP BY node_created, node_created_1
ORDER BY node_created_1 ASC

Should the GROUP BY field also have a DATE_FORMAT convert with the format "Y-M-d".

I will try to revert to the unpatched version and see if still the same.

I noticed that the raw SQL query adds another field node.created as node_created_1, so the table looks like this:

node_created_1 | node_created | nid
1432413881 | 2015-May-24 | 1
1432414239 | 2015-May-24 | 1
1432412412 | 2015-May-24 | 1

(and also included in the group by statement). No aggregation made.

I tried to make raw query tests, it should be only like this;

SELECT DATE_FORMAT((DATE_ADD('19700101', INTERVAL node.created SECOND) + INTERVAL 28800 SECOND), '%Y-%m-%d') AS node_created, COUNT(node.created) AS node_created_1
FROM node
WHERE (( (node.status = '1') AND (node.created >= 1432504644) ))
GROUP BY node_created
ORDER BY node_created ASC

node_created | nid
2015-05-24 | 185
2015-05-25 | 231
2015-05-26 | 174

It works great. I start to see the list I want.

---

I hope this makes sense... what should be the next step here?
IF we can somehow remove this unnecessary column, and correct the GROUP BY line to only use the one with the "DATE_FORMAT", (and arrange the sequence of the fields"

Adding more to my observations;

The aggregation breaks if we add a SORT CRITERIA
Make sure to use Numerical format, as it works well with the default sorting

I think this should confirm the patch works fine :)

SELECT node.created AS node_created_1, DATE_FORMAT((DATE_ADD('19700101', INTERVAL node.created SECOND) + INTERVAL 28800 SECOND), '%Y-%m-%d') AS node_created, COUNT(DISTINCT node.title) AS node_title
FROM 
{node} node
WHERE (( (node.status = '1') AND (node.type IN  ('sm9_incident')) AND (node.created >= 1432741229) ))
GROUP BY node_created, node_created_1
ORDER BY node_created_1 ASC

SELECT DATE_FORMAT((DATE_ADD('19700101', INTERVAL node.created SECOND) + INTERVAL 28800 SECOND), '%Y-%m-%d') AS node_created, COUNT(DISTINCT node.title) AS node_title
FROM 
{node} node
WHERE (( (node.status = '1') AND (node.type IN  ('sm9_incident')) AND (node.created >= 1432741288) ))
GROUP BY node_created

I test the patch #42, and works great for me, thanks !

After applying #42 and flushing the caches i get

Warning: require_once(/httpdocs/sites/all/modules/views_date_format_sql/includes/views_date_format_sql_handler_field_date.inc): failed to open stream: No such file or directory in _registry_check_code() (line 3186 of /httpdocs/includes/bootstrap.inc).

the includes folder contains the following files
-rw-r--r-- 1 root root 1931 Jan 15 09:57 views_date_format_sql_handler_date_field.inc
-rw-r--r-- 1 root root 1830 Jan 15 09:57 views_date_format_sql_handler_date.inc

EDIT:
I had to restart apache which runs with an apc cache. Now the error is gone

Is there a trick to make it work with charts?

Is there a patch for latest stable 7.x-3.3?

Here is an updated patch. This is a somewhat different... it takes into account the changes made in 7.x-3.2 and 7.x-3.3 to improve the performance of views_date_format_sql.views.inc. Please let me know if there are any issues.

Also the module branches need to be fixed. Right now, master is pointed at 7.x-3.2, and 7.x-3.3 is it's own tag. @zany, I'd appreciate if you can get this resolved, thanks.

I run into an error while patching the module (7x-3.3).

patching file views_date_format_sql.info
Hunk #1 FAILED at 6.
1 out of 1 hunk FAILED -- saving rejects to file views_date_format_sql.info.rej

views_date_format_sql.info.rej:

--- views_date_format_sql.info
+++ views_date_format_sql.info
@@ -6,4 +6,5 @@
 files[] = views_date_format_sql.module

 ; Views handlers
-files[] = includes/views_date_format_sql_handler_field_date.inc
+files[] = includes/views_date_format_sql_handler_date.inc
+files[] = includes/views_date_format_sql_handler_date_field.inc

After patching views_date_format_sql.info manually I can confirm that the patch is working for me.

@progalla, I cannot reproduce your error. I downloaded a clean copy of 7.x-3.3, re-applied patch #50, and have no issues.

Were you possibly using a copy of 7.x-3.2, or had you upgraded from 3.2 to 3.3? I'd appreciate if you could download a clean copy and see if you can cause it to happen again. I'm not seeing any problems.

I had the same patching error and was able to resolve it by removing the single space on line 248 of the patch file. The error was encountered using the command "patch -p1 < views_date_format_sql-date_field_support-1830436-50.patch" on Ubuntu Server 14.04.

Very odd, I know that patch applied cleanly at some point in the past. Not sure what changed. Here is a new version, and below is confirmation that it is patching cleanly.

$ git apply -v ../views_date_format_sql-date_field_support-1830436-54.patch
Checking patch includes/views_date_format_sql_handler_date.inc...
Checking patch includes/views_date_format_sql_handler_date_field.inc...
Checking patch includes/views_date_format_sql_handler_field_date.inc...
Checking patch views_date_format_sql.info...
Checking patch views_date_format_sql.module...
Checking patch views_date_format_sql.views.inc...
Applied patch includes/views_date_format_sql_handler_date.inc cleanly.
Applied patch includes/views_date_format_sql_handler_date_field.inc cleanly.
Applied patch includes/views_date_format_sql_handler_field_date.inc cleanly.
Applied patch views_date_format_sql.info cleanly.
Applied patch views_date_format_sql.module cleanly.
Applied patch views_date_format_sql.views.inc cleanly.

#54 failed for me for 3.3:

patch -p 1 < ../../../patches/views_date_format_sql-date_field_support-1830436-54.patch 
patching file includes/views_date_format_sql_handler_date.inc
patching file includes/views_date_format_sql_handler_date_field.inc
patching file includes/views_date_format_sql_handler_field_date.inc
patching file views_date_format_sql.info
Hunk #1 FAILED at 6.
1 out of 1 hunk FAILED -- saving rejects to file views_date_format_sql.info.rej
patching file views_date_format_sql.module
patching file views_date_format_sql.views.inc
Hunk #1 FAILED at 10.
1 out of 1 hunk FAILED -- saving rejects to file views_date_format_sql.views.inc.rej

after applying it manually, it works though.

Every time I try to patch in cygwin I get the message:

The next patch would delete the file a/includes/views_date_format_sql_handler_field_date.inc,
which does not exist! Assume -R? [n]

Any ideas?
I looked at manually patching but it's got me stumped...
Thanks

@ignelius, not sure what to tell you, I don't use cygwin. The file has to exist, it's part of the module: http://cgit.drupalcode.org/views_date_format_sql/tree/includes/views_dat...

Maybe you're not patching from the right location in the directory.

Thanks @ron_s
Yes the file is definitely there.
I downloaded the patch file into the module folder, navigated there in cygwin and then input:
patch -p0 < views_date_format_sql-date_field_support-1830436-54.patch
And get the error message above (#56).
I'm doing this on an isolated module folder (ie: outside of a drupal installation), maybe that's the problem.
If anyone's familiar with CygWin would really appreciate any tips, thank you.

Or a patched version of the module for download would be proper awesome ;)

I tried #29 and it worked for my situation, thanks.

@ron_s Your 3.3 must be different from the download on the project page or something... Could it be that the 3.3 tag is moved? Both #50 and #54 fails on the 3.3 download from d.o.

[fuzzy76@w520:tmp] wget https://ftp.drupal.org/files/projects/views_date_format_sql-7.x-3.3.tar.gz
--2016-07-07 14:56:31--  https://ftp.drupal.org/files/projects/views_date_format_sql-7.x-3.3.tar.gz
Length: 8482 (8.3K) [application/octet-stream]
Saving to: ‘views_date_format_sql-7.x-3.3.tar.gz’
2016-07-07 14:56:31 (13.7 MB/s) - ‘views_date_format_sql-7.x-3.3.tar.gz’ saved [8482/8482]
[fuzzy76@w520:tmp] tar zxf views_date_format_sql-7.x-3.3.tar.gz 
[fuzzy76@w520:tmp] cd views_date_format_sql
[fuzzy76@w520:views_date_format_sql] wget https://www.drupal.org/files/issues/views_date_format_sql-date_field_support-1830436-54.patch
--2016-07-07 14:57:07--  https://www.drupal.org/files/issues/views_date_format_sql-date_field_support-1830436-54.patch
Length: 11679 (11K) [text/plain]
Saving to: ‘views_date_format_sql-date_field_support-1830436-54.patch’
2016-07-07 14:57:07 (26.5 MB/s) - ‘views_date_format_sql-date_field_support-1830436-54.patch’ saved [11679/11679]
[fuzzy76@w520:views_date_format_sql] git apply -v views_date_format_sql-date_field_support-1830436-54.patch
Checking patch includes/views_date_format_sql_handler_date.inc...
Checking patch includes/views_date_format_sql_handler_date_field.inc...
Checking patch includes/views_date_format_sql_handler_field_date.inc...
Checking patch views_date_format_sql.info...
error: while searching for:
files[] = views_date_format_sql.module

; Views handlers
files[] = includes/views_date_format_sql_handler_field_date.inc

error: patch failed: views_date_format_sql.info:6
error: views_date_format_sql.info: patch does not apply
Checking patch views_date_format_sql.module...
Checking patch views_date_format_sql.views.inc...
error: while searching for:
function views_date_format_sql_views_data_alter(&$data) {
  // Loops through fields definitions looking for date fields
  // and change the standard date handler with our own.
  foreach ($data as $module => $table) {
    foreach ($table as $id => $field) {
      if (isset($field['field']['handler']) && $field['field']['handler'] == 'views_handler_field_date') {
        $data[$module][$id]['field']['handler'] = 'views_date_format_sql_handler_field_date';
      }
    }
  }

error: patch failed: views_date_format_sql.views.inc:10
error: views_date_format_sql.views.inc: patch does not apply

@fuzzy76, patches are never written against code downloaded from the project page. They are pulled from a Git version control branch:

https://www.drupal.org/project/views_date_format_sql/git-instructions

git clone --branch master https://git.drupal.org/project/views_date_format_sql.git

@ron_s I was confused by the fact that you in #50 said the patch was based off work in both 3.2 and 3.3, and in #52 said you applied it to a downloaded copy of 3.3. But the master branch only contains 3.2.

@fuzzy76, please see the comment I made in #50:

Also the module branches need to be fixed. Right now, master is pointed at 7.x-3.2, and 7.x-3.3 is it's own tag. @zany, I'd appreciate if you can get this resolved, thanks.

A patch can only be created against what is available in git as a branch, and there is no 7.x-3.x-dev branch to work against. As I said in follow-up comments, I am able to get the patch to apply cleanly to 7.x-3.2 and 7.x-3.3. However, I wouldn't be surprised if some others might have issues if they don't have a similar dev environment to mine.

The reality is until @zany (or @joelpittet, or someone else) actually updates the master branch or creates a formal 7.x-3.x-dev branch, and your environment does not allow you to patch the module cleanly, you'll have to do it manually.

@ron_s, I didn't catch #50, sorry. Fixed. I also put your patch #54 into 7.x-3.x so it's easier to test and hopefully RTBC.

Thank you @zany!

Also setting to 7.x-3.x-dev, so everyone knows they should be testing against this version.

According to https://www.drupal.org/node/998076 date fields are not converted to UTC when being stored to database., and therefore I get shifted results when I use _Views Date Format SQL_ formatting. Probably we should force the offset to be zero for the fields.

Confirming that 7.x-3.x works for me with a Date field.

For others who stumble onto this post: If you don't see a check box for "Use SQL to format date" when you configure the date field in your view, make sure you use the -dev branch of the module. Then, if using a Sort for your date field breaks the aggregation, use the tip in #26 and set the aggregation to "Minimum" instead of "Group results together."

Thanks for the fix @alex-bukach. Pushed to 7.x-3.x.

Can anyone else using this report it working like @potassiumchloride -- a few satisfied users and I'll finally mark this stable.

The patch is supposed to be working with unixtimestamp date? I haved tested 7.x-3.x-dev and is not working for me. I tried #26 and neither worked.

I do not see the check box even when following #69

Where is the checkbox supposed to appear? I'm using the dev branch and I've cleared the cache.

Any Updates?

The 7.x-3.x-dev version accurately reflects patch #54. I'm also attaching a minor patch to fix a couple of typos I had in the original. Thanks.

@rkent87, it has to be on a date content field created using the Date module. From your screen shot, it doesn't look like that's your source. See my attached image.

Thanks

Any chance of getting this released any time soon? Getting these patches applied to a stable release is a major pain. :-/

@fuzzy76 the 7.x-3.x-dev is at the latest patch.

Yes, but I won't use dev releases in production.

Well, if that's the policy. Pull the 3 commits (labeld "Issue #1830436") from the 7.x-3.x git and apply on top of the 7.x-3.3 tag. This will give you the same source without using -dev. Or even quicker: use 7.x-3.x and reset --soft HEAD^^, maybe.

Or maybe pin your drush make to 2dc5920. Otherwise this issue just needs a few solid RTBC to be released.

Installing Dev fixed the issue with the checkbox showing. Thank you

Simply a reroll of #54 + #76 for 3.3

Updated to latest dev and seems to be working great, using date fields on field collections.
Thanks!

When will Drupal 9 be available for this module?

This module was a quick hack until Views picked this needed change up -- 10 years ago. There was never a stable 8.x release and I don't "envision" a 9.x release. I'm not using this feature anymore. If there are tested patches or RTBC I'll merge that anytime, though.