PostgreSQL - Countdown field error - unix_timestamp() does not exist

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

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.

    $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.

Hi people

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

Thanks

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.

4: scheduler-1708488-postgres-4.patch queued for re-testing.

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

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

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.

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

Excellent. Committed. 5649e31

Great work, thanks!