Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

author
message
[Level:0]cristiroma

Post subject: Problem handling datetime objects.

registered: 03/01/2011

IP: *.9.55.194

views: 48

Hello,

I have an issue with date handling in Cubrid. Cubrids' 'null' date is 0001-01-01 00:00:00. However when I use PHP to retrieve such a column it returs: 1-1-1 0:0:0.000

Let's suppose I have the following scenario:

1) create a table with a datetime column
2) insert 0001-01-01 00:00:00 into the column
3) use the table from a PHP script
$nullDate = '0001-01-01 00:00:00';
$rs = cubrid_query('SELECT `date` FROM `test`');
$arr = $rs->FetchRow();
assert($arr[0] == $nullDate); // FAILS

$arr[0] = 1-1-1 0:0:0.000 !!!

Platform:
PHP 5.3.2-1ubuntu4.2 with Suhosin-Patch (cli) (built: May 13 2010 20:01:00)
I use the cubrid.so compiled from sources: https://sourceforge.net/projects/cubrid ... z/download
Database is CUBRID 3.0: https://sourceforge.net/projects/cubrid ... h/download

Quote
[Level:3]eye

# Post subject:Re: Problem handling datetime objects.

profile

registered: 05/31/2011

IP: *.91.137.50

Hello Cristian,

Here is the answer for your question related to the DATE data type handling.
https://sourceforge.net/projects/cubrid ... ic/3868137

Briefly, the 1-1-1 0:0:0.000 result is received by PHP API from the CCI Library, which stores the DATE value into T_CCI_DATE structure.


typedef struct
{
short yr;
short mon;
short day;
short hh;
short mm;
short ss;
short ms;
} T_CCI_DATE;


The short data type, converts 0001 into just 1. Therefore, we receive the above format.

If you think this is not the way it should be, please, let me know. I will make sure the CUBRID developer address this problem.

1) Have you encountered this issue while porting Joomla?
2) What did you do to pass by this issue?

Quote
[Level:0]cristiroma

# Post subject:Re: Problem handling datetime objects.

profile

registered: 03/01/2011

IP: *.9.55.194

Hi everyone,

Even if it's not a bug itself, it has some undersirable consequences (at least in my case). For example:

class CubridDB {
$nullDate = '0001-01-01 00:00:00';
function getNullDate() {
return $this->nullDate;
}
}
$db = new CubridDB(...);

/* INSERT */
$ob->published_date = $db->getNullDate();
insert($ob);

/* SELECT */
if($db->getNullDate() == $ob->lastPublishedDate ) {
echo 'Unknown';
}


The code above would now work naturally and I have to write something like:


if($ob->lastPublishedDate == $db->getNullDate() || $ob->lastPublishedDate == '1-1-1 0:0:0.000') {
echo 'Unknown';
}


I would it's say rather an inconsistent behavior because when saving I have to set a certain value and while selecting/comparing I have to use with a different value.

Also, I have noticed that non-null values are transformed to integer such as 2005-8-9 10:0:0.000. And this behaviour is inconsistent with other databases such as MySQL or PostgreSQL to that use standard timestamp format YYYY-MM-DD HH:MM:SS.

* http://dev.mysql.com/doc/refman/5.0/en/timestamp.html
* http://www.postgresql.org/docs/8.0/static/datatype-datetime.html#DATATYPE-DATETIME-TIME-TABLE

Please note that I'm not familiar with Cubrid internals (CCI) or consequences, but what do you think?

I have encountered this scenario in porting Joomla and to overcome I have hard-coded temporarily as:
if($ob->lastUpdate == '1-1-1 0:0:0.000') ...

Regards,
Cristian.

Quote
[Level:3]eye

# Post subject:Re: Problem handling datetime objects.

profile

registered: 05/31/2011

IP: *.91.137.50

If you set the nullDate to '1-1-1 0:0:0.000' like

class CubridDB {
$nullDate = '1-1-1 0:0:0.000';
...


Will CUBRID accept when you *INSERT* this value?

I will report this behavior to the Dev Team.

Quote
[Level:3]cottonspan

# Post subject:Re: Problem handling datetime objects.

profile

registered: 05/31/2011

IP: *.91.137.78

https://sourceforge.net/apps/trac/cubrid/ticket/131

This issue has been reported to the Dev-team and you can track the issue on the above link.
You can also directly report the issue to the cubridinterface project forum(interface relavant issues) otherwise the cubrid project trac(cci and server issues).

Thank you.

Quote
[Level:0]cristiroma

# Post subject:Re: Problem handling datetime objects.

profile

registered: 03/01/2011

IP: *.9.55.194

Yes, I can confirm that it works inserting '1-1-1 0:0:0.000' in DATETIME column as NULL. I will modify the code appropriately...

Why didn't I think of that ... I wonder ... :oops:

Quote




You are either using a very old browser or a browser that is not supported.
In order to browse cubrid.org you need to have one of the following browsers:



Internet Explorer: Mozilla Firefox: Google Chrome: