[Koha-bugs] [Bug 21105] oai.pl returns invalid earliestDatestamp

[bugzilla-daemon at bugs.koha-community.org]

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=21105

--- Comment #2 from Rudolf Byker <rudolfbyker at gmail.com> ---
Currently, the code to get the earliestDatestamp looks like this:

earliestDatestamp   => _get_earliest_datestamp() || '0001-01-01T00:00:00Z',

and

sub _get_earliest_datestamp {
    my $dbh = C4::Context->dbh;
    my ( $earliest ) = $dbh->selectrow_array("SELECT MIN(timestamp) AS earliest
FROM biblio" );
    return $earliest
}

There are two problems here:

1. From the MySQL docs: "MySQL converts TIMESTAMP values from the current time
zone to UTC for storage, and back from UTC to the current time zone for
retrieval." Similarly for MariaDB: "If a column uses the TIMESTAMP data type,
then any inserted values are converted from the session's time zone to
Coordinated Universal Time (UTC) when stored, and converted back to the
session's time zone when retrieved." But we MUST get this as a UTC value
according to the OAI-PMH spec (see
http://www.openarchives.org/OAI/openarchivesprotocol.html#Identify and
http://www.openarchives.org/OAI/openarchivesprotocol.html#Dates )

2. It's formatted in the default MySQL way, which is YYYY-MM-DD hh:mm:ss (local
time) rather than YYYY-MM-DDThh:mm:ssZ (UTC).

I don't really know Perl, but I can suggest a SQL query that should fix the
problem:

SELECT DATE_FORMAT(CONVERT_TZ(MIN(timestamp), 'SYSTEM', '+00:00'),
'%Y-%m-%dT%H:%i:%SZ') AS earliest FROM biblio;

Example results:

Before:

+---------------------+
| earliest            |
+---------------------+
| 2012-11-03 13:41:32 |
+---------------------+

After:

+----------------------+
| earliest             |
+----------------------+
| 2012-11-03T11:41:32Z |
+----------------------+

-- 
You are receiving this mail because:
You are the assignee for the bug.
You are watching all bug changes.