Hello !

Thanks you for this uselful module.

I am using PostgreSQL 8.

Under Views 3.3, if i try to use the countdown field, i obtain this message:

SQLSTATE[42883]: Undefined function: 7 ERROR: function unix_timestamp() does not exist LINE 1: ...e_entity_type, IF(unpublish_on AND unpublish_on > UNIX_TIMES... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts.

I am not sure if PostgreSQL is the problem.

Files: 
CommentFileSizeAuthor
#12 scheduler-1708488-postgres-12.patch1.41 KBjonathan1055
PASSED: [[SimpleTest]]: [MySQL] 348 pass(es).
[ View ]

Comments

jonathan1055’s picture

Title:PostgreSQL - Countdown field views Error» PostgreSQL - Countdown field error - unix_timestamp() does not exist
Status:Active» Closed (cannot reproduce)

Hi Jibus,

Is this still a problem for you? I've not had any experience with PostgreSQL databases, but it seems you are saying that the function unix_timestamp() is not implemented. I guess there must be an alternative, but this would usually be handled in Drupal's database abstraction layer.

If you are still getting this error, please re-open the issue and let us know what Drupal version you are running and what PostgreSQL version you have.

Thanks
Jonathan

jonathan1055’s picture

Status:Closed (cannot reproduce)» Active

Just searched Drupal.org and this has been reported before:
#690322: Postgresql error: Function unix_timestamp() does not exist
#1538782: MySQL-specific unix_timestamp() is used in og_mailinglist.install

Seems that unix_timestamp() is a MySQL-specific function, and that PHP's time() is the preferred alternative which does the same thing. However, the actual line in scheduler_handler_field_scheduler_countdown.inc is within the query() function of the scheduler_handler_field_scheduler_countdown class.

<?php
    $this
->field_alias = $this->query->add_field(NULL, 'IF(' . $time_field . ' AND ' . $time_field . ' > UNIX_TIMESTAMP(), ' . $time_field . ' - UNIX_TIMESTAMP(), NULL)', $this->table_alias . '_' . $this->field);
?>

Not sure how the php function would work here because it needs to be executed at run-time.

pmichelazzo’s picture

Issue summary:View changes

Hi people

Nobody with a solution for this problem? I have the same issue with the Scheduler module with PostgreSQL v9

Thanks

stefan.r’s picture

Status:Active» Needs review
StatusFileSize
new1.29 KB
PASSED: [[SimpleTest]]: [MySQL] 348 pass(es).
[ View ]

From the Drupal docs:

For improved performance, it is highly recommended that any calls to time() are replaced with REQUEST_TIME, a defined constant which will always return the UNIX timestamp from the start of the current request. If you absolutely need to get the current time, you can still use time() but it is not recommended.

stefan.r’s picture

Issue tags:+PostgreSQL

Status:Needs review» Needs work

The last submitted patch, 4: scheduler-1708488-postgres-4.patch, failed testing.

stefan.r’s picture

Version:7.x-1.0» 7.x-1.x-dev
Status:Needs work» Needs review
stefan.r’s picture

jonathan1055’s picture

Hi stefan.r
Thanks for your patch. I've been away for a few days, so not had chance to look at this yet. I will review it soon.
Jonathan

jonathan1055’s picture

The change works fine. I tested the old and new countdowns in a the same view and they give the same result, apart from when the time was really near, when I noticed a one-second difference. But that does not matter at all.

I've only tested on MySQL, so if you confirm this works ok on PostgreSQL please mark the issue RTBC and I'll commit the change.

Jonathan

stefan.r’s picture

StatusFileSize
new1.33 KB
PASSED: [[SimpleTest]]: [MySQL] 348 pass(es).
[ View ]

Thanks Jonathan, this patch actually didn't work on PostgreSQL as there was still an IF() statement.

Looks like time() is actually more accurate than REQUEST_TIME in this case as the user expects the countdown to be from when the page is displayed, not from when it's requested. So the 1 second difference should be gone now too :)

Revised patch attached, if you can test that one on MySQL as well we can RTBC this.

jonathan1055’s picture

StatusFileSize
new1.41 KB
PASSED: [[SimpleTest]]: [MySQL] 348 pass(es).
[ View ]

I did not know that IF() is also not supported in PostgreSQL, so thanks for checking that. I have removed the '$time_field IS NOT NULL' check because '$time_field > now' should cater for that.

Regarding time() I think it is best to stick with REQUEST_TIME for two reasons: (a) better performance if a view has lots of scheduled nodes and (b) to be consistent across all the results within a view.

This patch works on MySQL - hopefully it will on PostgreSQL and you can mark it RTBC

stefan.r’s picture

Status:Needs review» Reviewed & tested by the community

  • Commit 5649e31 on 7.x-1.x authored by stefan.r, committed by jonathan1055:
    Issue #1708488 by stefan.r, jonathan1055: Make countdown field...
jonathan1055’s picture

Status:Reviewed & tested by the community» Fixed

Excellent. Committed. 5649e31

pfrenssen’s picture

Great work, thanks!

Status:Fixed» Closed (fixed)

Automatically closed - issue fixed for 2 weeks with no activity.