This is related to http://drupal.org/node/122801

I found some possible mistakes in date module > date.inc > date_sql() function. First there is no $db_type 'postgres' but 'pgsql'. Second for compatibility with PostgreSQL older than version 8 (7.4 is still in use) we need another approach into unix timestamp (in postgresql terminology 'epoch' or 'unixtime'). I now experimenting with code...

Some examples:

-- there is no function TIMESTAMP in postgresql but there is TO_TIMESTAMP(double precission) (for PostgreSQL 8 & >)
-- for get unix timestamp (epoch) from postgresql timestamp >
SELECT EXTRACT('epoch' FROM NOW()) AS unixtime;

-- for get postgresql timestamp from unix timestamp (epoch)
-- - with local timezone (actual time)
SELECT TIMESTAMPTZ 'epoch' + EXTRACT('epoch' FROM NOW()) * INTERVAL '1 second';
-- where is EXTRACT('epoch' FROM NOW()) actual unix timestamp (or column with unix timestamp)

-- - without local timezone (timezone 0)
SELECT TIMESTAMP 'epoch' + EXTRACT('epoch' FROM NOW()) * INTERVAL '1 second';
-- where is EXTRACT('epoch' FROM NOW()) actual unix timestamp (or column with unix timestamp)

I have tried this queries in PostgreSQL version 7.4.15 and 8.2.1 and it's working.

CommentFileSizeAuthor
#10 date.inc__0.patch968 byteshavran
#2 date.inc_.patch3.02 KBhavran

Comments

havran’s picture

havran commented

For converting unix timestamp to postgresql timezone is much simpler way which work both on PostgreSQL 7.4 and 8. For example:

SELECT node.created::ABSTIME FROM node node;
havran’s picture

havran commented
Status: Active » Needs work
StatusFileSize
new date.inc_.patch3.02 KB

Here is first, partialy working patch for this issue.

karens’s picture

karens commented

I knew there were probably problems in the Postgres code. I created it by looking at postgres documentation and digging around in postgres forums but had no way to test it. I'm glad to get some help on this and will gladly commit any fixes needed. The 'postgres' instead of 'pgsql' mistake was a dumb mistake on my part. I probably made it in one place then just copied it everywhere instead of going back to the source. I may have this mistake elsewhere, so I'll have to look around my other code.

The patch looks good to me. Let me know when you're ready for me to commit something.

havran’s picture

havran commented

For me work patch (for year) ok. But because here http://drupal.org/node/125509 is some bad dates in query, i get still some error PostgreSQL.

I think here is one more thing: in date.inc > function date_server_zone_adj() im not sure for this lines:

      case ('pgsql'):
        // The TIMEZONE function returns the timezone adjustment in seconds.
        $server_zone_adj = db_result(db_query("TIMEZONE"));
        break;

PostgreSQL have no TIMEZONE function. This

SELECT extract('timezone' from now());

return 'The time zone offset from UTC, measured in seconds. Positive values correspond to time zones east of UTC, negative values to zones west of UTC.' - maybe this useful for date_server_zone_adj() (if i understood funcionality this function :)).

karens’s picture

karens commented

What's the difference between $field::ABSTIME::INT4 and $field::ABSTIME? Something I read somewhere seemed to indicate that 'INT4' was important. I can believe I got the TIMESTAMP part wrong since I deduced that rather than finding something specific that used it.

karens’s picture

karens commented 
SELECT extract('timezone' from now());

Yes, that's what I was trying to get, I'll make that change.

karens’s picture

karens commented

There are going to be some related adjustments, like the need fix the SQL in the issue you point to, but I'm going ahead and committing this much of the code since it almost certainly is better than what was there.

Still outstanding is the question about whether or not ::INT4 belongs in the conversion from unixtime.

havran’s picture

havran commented 
SELECT 0::ABSTIME::INT4; -- return 0
SELECT 0::ABSTIME; -- return 1970-01-01 01:00:00+01 (postgresql timestamp with timezone)

I think node fields with dates is always int type (unix timestamp) - 0::ABSTIME::INT4 convert from int through postgre timestamp into int. I think you need postgre timestamp in this step.

karens’s picture

karens commented
Status: Needs work » Fixed

OK, sounds like we do not want INT4 in there, so I just committed a fix for that. I think that is all the fixes for the basic postgres sql handling, the other issue is a Calendar module issue so I am going to mark this fixed. If we missed anything you can reopen.

havran’s picture

havran commented
Status: Fixed » Needs review
StatusFileSize
new date.inc__0.patch968 bytes

I have two small PostgreSQL related fixes for DOW (day of week) and DOY (day of year) cases in date.inc > in date_sql() function... Patch attached.

Now i get correct year view http://www.fem.uniag.sk/havran/testcalendar/2007, month view (like januar) http://www.fem.uniag.sk/havran/testcalendar/2007/1, day view http://www.fem.uniag.sk/havran/testcalendar/2007/02/27.

But still is problem with februar http://www.fem.uniag.sk/havran/testcalendar/2007/1 and i have problem with showing correct weeks - like this (week 9 in februar) http://www.fem.uniag.sk/havran/testcalendar/2007/W09.

How can i help with this?

havran’s picture

havran commented

And i have latest copy for calendar 5.x.1.x and date 5.x.1.x - i get it (through cvs up) 5 minuts ago.

karens’s picture

karens commented
Status: Needs review » Fixed

I committed the patch, thanks!

Not sure what is the February problem, can't tell from your link.

The doubled up week is a separate issue that has come up before (I thought it was fixed). Please report that in a separate thread since it has nothing to do with postgres. I think it is related to using Monday as the first day of the week. See if it goes away if you set Sunday as the first day of the week in your site configuration.

I'm marking this fixed, meaning I think the postgres issues are fixed.

havran’s picture

havran commented

> The doubled up week is a separate issue that has come up before (I thought it was fixed). Please report that in a separate thread since it has nothing to do with postgres. I think it is related to using Monday as the first day of the week. See if it goes away if you set Sunday as the first day of the week in your site configuration.

You get it. I set first day into Sunday and now week show correct...

Anonymous’s picture

(not verified) commented
Status: Fixed » Closed (fixed)