Please, correct (bug) reading date/time from PostgreSQL (TPgSQLStatement)

ROOT
1

Good afternoon.
I’m using ROOT (ROOT 5.34.36) classes to work with our PostgreSQL database (8.4).
I write TDatime variable to the database and then immediately read it back via TSQLStatement::SetDatime/GetDatime functions. But it returns another date/time value, it’s different for 16 hours.
It’s concerned with wrong "void TPgSQLStatement::ConvertTimeToUTC(const TString &PQvalue, Int_t& year, Int_t& month, Int_t& day, Int_t& hour, Int_t& min, Int_t& sec) " function.
It checks +/- UTC and finds the minus inside the date part, e.g. “2004-10-19 10:23:54” (compare with "“2004-10-19 10:23:54-02"”). Then it checks position of the minus relative ‘.’. But there is no any ‘.’
I propose to use ‘:’ symbol (using in the time part) - it works.
i.e.
instead of:

void TPgSQLStatement::ConvertTimeToUTC(const TString &PQvalue, Int_t& year, Int_t& month, Int_t& day, Int_t& hour, Int_t& min, Int_t& sec)
{
   // Convert timestamp value to UTC if a zone is included.

   Ssiz_t p = PQvalue.Last('.');

use:

void TPgSQLStatement::ConvertTimeToUTC(const TString &PQvalue, Int_t& year, Int_t& month, Int_t& day, Int_t& hour, Int_t& min, Int_t& sec)
{
   // Convert timestamp value to UTC if a zone is included.

   Ssiz_t p = PQvalue.Last(':');

With respect, Konstantin.

2

Hi Konstantin,

thanks for the report and the fix proposal.
This will be tackled very soon. I’ll keep you posted.

Cheers,
Danilo

3

Good morning.
Have you corrected this bug in the ROOT repo?
I don’t see it in the patches.
Thank you

4

Hi Konstantin,

unfortunately not.
Could you share the test for this feature?

Danilo

5

Hi.
Excuse me, how can I prepare the test? Should I write the special script deploying PostgreSQL libraries and test database with test inserting?

P.S. e.g. I have datetimes for run #930 from 2016-12-21 15:13:22 to 2016-12-21 15:28:23 in my PostgreSQL database (screenshot from pgadmin attached), but TSqlStatement::GetDatime returns “Tue Dec 20 18:13:22 2016” and “Tue Dec 20 18:28:23 2016” because of this bug.

pgadmin.png1740×156 37.5 KB

6

Hi,

one way would be to provide all the input and the code which allows to recreate the issue in an isolated environment.

D

7

Hi.
This bug is still remaining in the last ROOT 6 version for the last years. Unfortunately, I have to patch our experiment software every installation to exclude this bug.

So, I try to clear this bug one more time:
In Postgresql we have the following timestamp format without timezone: YYYY-MM-DD HH:MM::SS (e.g. 2016-12-21 15:13:22), as it is shown on the above picture.
When we get the datime from the PostgreSQL database, we get the wrong value every time. It happens because of the following bug in TPgSQLStatement.cxx.
We use:

Bool_t TPgSQLStatement::GetDatime(Int_t npar, Int_t& year, Int_t& month, Int_t& day, Int_t& hour, Int_t& min, Int_t& sec)
{
TString val=PQgetvalue(fStmt->fRes,fIterationCount,npar);
TDatime d = TDatime(val.Data());
year = d.GetYear();
month = d.GetMonth();
day= d.GetDay();
hour = d.GetHour();
min = d.GetMinute();
sec= d.GetSecond();
ConvertTimeToUTC(val, year, month, day, hour, min, sec);
return kTRUE;
}

It is ok but it uses ConvertTimeToUTC function:

void TPgSQLStatement::ConvertTimeToUTC(const TString &PQvalue, Int_t& year, Int_t& month, Int_t& day, Int_t& hour, Int_t& min, Int_t& sec)
{
Ssiz_t p = PQvalue.Last(‘.’);
// Check if timestamp has timezone
TSubString *s_zone = nullptr;
Bool_t hasZone = kFALSE;
Ssiz_t tzP = PQvalue.Last(‘+’);
if ((tzP != kNPOS) && (tzP > p) ) {
s_zone = new TSubString(PQvalue(tzP+1,PQvalue.Length()-tzP));
hasZone=kTRUE;
} else {
Ssiz_t tzM = PQvalue.Last(‘-’);
if ((tzM != kNPOS) && (tzM > p) ) {
s_zone = new TSubString(PQvalue(tzM+1,PQvalue.Length()-tzM));
hasZone = kTRUE;
}
}
if (hasZone == kTRUE) {
…

What can we see:
First, p variable is equal the position of the point (‘.’) in the datetime string. We have no points, so p == npos (-1).
Then the function searches for the last plus (‘+’) symbol. It is absent, so then it searches for the last minus (‘-’) symbol and it is found in the date part (YYYY-MM-DD). Ok, now the conditions (tzM != kNPOS is true because we have found ‘-’) and (tzP > p is true because p is equal -1) are true, so:

hasZone flag is set to kTRUE!

Then in condition: if (hasZone == kTRUE) - the time is shifted but it is absolutely wrong because we have no timezone in our case.

Thank you

8

See https://sft.its.cern.ch/jira/browse/ROOT-9524 - but I believe it has been addressed already by Danilo, and the patch is in v6.14.