day granularity on sort by Post Date

Classifying this as a support request because I'm not completely sure that this is a bug. Apologies if this has been addressed before.

I am using Drupal 6.3, Views 6.x-2.0-rc1, PHP 5.2, and PostgreSQL 8.1.

If you create a view with "Node: Post date" as sort criteria, and set the granularity on this to 'day', Views does not seem to consider time zones when resolving which day a timestamp is on. This is especially apparent if you are using the Post Date set on 'Grouping field' with the List style and have date format set to only include up to day (ex: l, F jS). The result is that nodes are improperly sorted, but correctly grouped.

I traced this down to the query which uses "TO_CHAR((node.created::ABSTIME + INTERVAL '-18000 SECONDS'), 'YYMMDD') AS node_created_day" in the select clause to truncate the date information down to YYMMDD.

A better select clause on PostgreSQL for getting YYMMDD might be "to_char(date_trunc('day', to_timestamp(node.created)), 'YYMMDD') AS node_created_day".

I was able to hack sort.handlers.inc to use this query on the day granularity stuff instead. Probably only works for PostgreSQL, probably not the 'right' way to fix this, but needed a workaround.

I've attached my workaround as a patch as well as my exported view.

Let me know if more information is needed.

Thanks much!