Postgresql expects a timestamp not an integer in to_cast function [#2822607]

Problem/Motivation

Postgresql 'to_char' function expects that first argument is a timestamp in order to convert the date to a string. Currently, date fields are stored as integer.

Create a new Content view output using fields
Add a date field such as Authored on date

Use date/time formatting ?????
...Fail condition

Proposed resolution

I see to options to resolve this issue:
- keep the database field definition unchanged and use the 'to_timestamp' function on the field.
- change the database field definition to use DATE type.

The attached patch resolves the issue by using the first approach.

Comment	File	Size	Author
#14	interdiff_11-14.txt	627 bytes	ridhimaabrol24
#14	2822607-14.patch	1.39 KB	ridhimaabrol24
#14	9.1.x: PHP 7.4 & MySQL 5.7 27,390 pass PHP 7.3 & PostgreSQL 10.12 27,385 pass, 16 fail
#11	2822607-11.patch	610 bytes	Hardik_Patel_12
#11	9.1.x: PHP 7.4 & MySQL 5.7 27,381 pass, 1 fail
#2	2822607-postgresql-timestamp-to-string.patch	679 bytes	mickaelperrin
#2	8.2.x: PHP 5.5 & MySQL 5.5 20,908 pass

Support from Acquia helps fund testing for Drupal Acquia logo

Comments

Comment #1

27 October 2016 at 15:15

mickaelperrin created an issue. See original summary.

Comment #2

mickaelperrin CreditAttribution: mickaelperrin as a volunteer commented 27 October 2016 at 15:22

Status:

Active

» Needs review

File	Size
2822607-postgresql-timestamp-to-string.patch	679 bytes
8.2.x: PHP 5.5 & MySQL 5.5 20,908 pass

Comment #3

27 October 2016 at 15:22

Version:

8.2.x-dev

» 8.3.x-dev

Drupal 8.2.6 was released on February 1, 2017 and is the final full bugfix release for the Drupal 8.2.x series. Drupal 8.2.x will not receive any further development aside from critical and security fixes. Sites should prepare to update to 8.3.0 on April 5, 2017. (Drupal 8.3.0-alpha1 is available for testing.)

Bug reports should be targeted against the 8.3.x-dev branch from now on, and new development or disruptive changes should be targeted against the 8.4.x-dev branch. For more information see the Drupal 8 minor version schedule and the Allowed changes during the Drupal 8 release cycle.

Comment #4

27 October 2016 at 15:22

Version:

8.3.x-dev

» 8.4.x-dev

Drupal 8.3.6 was released on August 2, 2017 and is the final full bugfix release for the Drupal 8.3.x series. Drupal 8.3.x will not receive any further development aside from critical and security fixes. Sites should prepare to update to 8.4.0 on October 4, 2017. (Drupal 8.4.0-alpha1 is available for testing.)

Bug reports should be targeted against the 8.4.x-dev branch from now on, and new development or disruptive changes should be targeted against the 8.5.x-dev branch. For more information see the Drupal 8 minor version schedule and the Allowed changes during the Drupal 8 release cycle.

Comment #5

27 October 2016 at 15:22

Version:

8.4.x-dev

» 8.5.x-dev

Drupal 8.4.4 was released on January 3, 2018 and is the final full bugfix release for the Drupal 8.4.x series. Drupal 8.4.x will not receive any further development aside from critical and security fixes. Sites should prepare to update to 8.5.0 on March 7, 2018. (Drupal 8.5.0-alpha1 is available for testing.)

Bug reports should be targeted against the 8.5.x-dev branch from now on, and new development or disruptive changes should be targeted against the 8.6.x-dev branch. For more information see the Drupal 8 minor version schedule and the Allowed changes during the Drupal 8 release cycle.

Comment #6

27 October 2016 at 15:22

Version:

8.5.x-dev

» 8.6.x-dev

Drupal 8.5.6 was released on August 1, 2018 and is the final bugfix release for the Drupal 8.5.x series. Drupal 8.5.x will not receive any further development aside from security fixes. Sites should prepare to update to 8.6.0 on September 5, 2018. (Drupal 8.6.0-rc1 is available for testing.)

Bug reports should be targeted against the 8.6.x-dev branch from now on, and new development or disruptive changes should be targeted against the 8.7.x-dev branch. For more information see the Drupal 8 minor version schedule and the Allowed changes during the Drupal 8 release cycle.

Comment #7

27 October 2016 at 15:22

Version:

8.6.x-dev

» 8.8.x-dev

Drupal 8.6.x will not receive any further development aside from security fixes. Bug reports should be targeted against the 8.8.x-dev branch from now on, and new development or disruptive changes should be targeted against the 8.9.x-dev branch. For more information see the Drupal 8 and 9 minor version schedule and the Allowed changes during the Drupal 8 and 9 release cycles.

Comment #8

27 October 2016 at 15:22

Version:

8.8.x-dev

» 8.9.x-dev

Drupal 8.8.7 was released on June 3, 2020 and is the final full bugfix release for the Drupal 8.8.x series. Drupal 8.8.x will not receive any further development aside from security fixes. Sites should prepare to update to Drupal 8.9.0 or Drupal 9.0.0 for ongoing support.

Bug reports should be targeted against the 8.9.x-dev branch from now on, and new development or disruptive changes should be targeted against the 9.1.x-dev branch. For more information see the Drupal 8 and 9 minor version schedule and the Allowed changes during the Drupal 8 and 9 release cycles.

Comment #9

Kristen Pol

she/her

English

Santa Cruz, CA, USA

CreditAttribution: Kristen Pol at Hook 42 for Hook 42 commented 14 July 2020 at 07:16

Version:	8.9.x-dev	» 9.1.x-dev
Status:	Needs review	» Needs work
Issue tags:		+Bug Smash Initiative, +Needs reroll

Patch does not apply to 9.1.x and needs reroll.

Comment #10

Hardik_Patel_12 CreditAttribution: Hardik_Patel_12 at QED42 for Drupal India Association commented 14 July 2020 at 07:50

Assigned:

Unassigned

» Hardik_Patel_12

Comment #11

Hardik_Patel_12 CreditAttribution: Hardik_Patel_12 at QED42 for Drupal India Association commented 14 July 2020 at 07:57

Assigned:	Hardik_Patel_12	» Unassigned
Status:	Needs work	» Needs review

File	Size
2822607-11.patch	610 bytes
9.1.x: PHP 7.4 & MySQL 5.7 27,381 pass, 1 fail

1 file was hidden/shown/deleted

File	Size
2822607-postgresql-timestamp-to-string.patch	679 bytes
8.2.x: PHP 5.5 & MySQL 5.5 20,908 pass

Re-rolling against 9.1.x , kindly review a patch.

Comment #12

Kristen Pol

she/her

English

Santa Cruz, CA, USA

CreditAttribution: Kristen Pol at Hook 42 for Hook 42 commented 14 July 2020 at 08:40

Issue tags:

-Needs reroll

Comment #13

14 July 2020 at 08:59

Status:

Needs review

» Needs work

The last submitted patch, 11: 2822607-11.patch, failed testing. View results

Comment #14

ridhimaabrol24 CreditAttribution: ridhimaabrol24 at Srijan | A Material+ Company for Drupal India Association commented 14 July 2020 at 10:23

Status:

Needs work

» Needs review

File	Size
2822607-14.patch	1.39 KB
9.1.x: PHP 7.4 & MySQL 5.7 27,390 pass PHP 7.3 & PostgreSQL 10.12 27,385 pass, 16 fail
interdiff_11-14.txt	627 bytes

Fixing failed test case! Please review!

Comment #15

Kristen Pol

she/her

English

Santa Cruz, CA, USA

CreditAttribution: Kristen Pol at Hook 42 for Hook 42 commented 14 July 2020 at 18:15

Issue tags:

+Needs manual testing

Thanks for the updates.

1) Patch applies cleanly.

2) Change is straightforward.

3) Tests pass.

4) Searching for TO_CHAR in core code only brings up this places covered by the patch.

5) Since this is in views, it seems like this could be tested manually via the UI so marking for testing.

Comment #16

mradcliffe

he/him

English

CreditAttribution: mradcliffe at Kosada commented 14 July 2020 at 18:25

Issue summary:	View changes
Issue tags:		+Global2020

I added the global2020 tag, and updated the issue summary with some steps to reproduce the issue in the user interface. I'm going off of memory here. So please double-check this and update the issue summary.

Comment #17

mradcliffe

he/him

English

CreditAttribution: mradcliffe at Kosada commented 14 July 2020 at 18:26

Issue tags:

+PostgreSQL

Added the PostgreSQL tag which we sometimes use to track pgsql driver issues.

Comment #18

Lendude

Dutch

Amsterdam

CreditAttribution: Lendude as a volunteer and at iO commented 16 July 2020 at 12:18

Status:	Needs review	» Needs work
Issue tags:		+Needs tests

We should add an automated test that fails on PostgreSQL

Comment #19

mindbet CreditAttribution: mindbet as a volunteer commented 21 July 2020 at 16:17

Status:

Needs work

» Needs review

I think this could actually be closed, without patching.

Either as 'cannot reproduce' or 'outdated'

I installed, on my local, Drupal 9.1.0-dev using PHP 7.3.20 and PostgresSQL 10.12

I did not implement any of the patches above.

I was able to add Timestamp and Date fields to a content type.
I created several nodes, and could enter values in both of these fields.

Then I created a simple view, and found that I could display these values, sort on these values, and change the date format of these values.

I also used the 'Authored on' field as part of the view, and could change the date format and sort on that value.

Comment #20

Kristen Pol

she/her

English

Santa Cruz, CA, USA

CreditAttribution: Kristen Pol at Hook 42 for Hook 42 commented 22 July 2020 at 05:55

Status:

Needs review

» Closed (cannot reproduce)

Thanks @mindbet! Based on your testing in #19, closing this as cannot reproduce. The manual testing you did seems to cover the issue summary notes and more.