#1496: pgsql fails with timestamp comparisons
---------------------------------------+------------------------------------
Reporter: Russ | Owner: romanb
Type: defect | Status: new
Priority: major | Milestone:
Component: Query/Hydration | Version: Trunk
Keywords: pgsq postgresql timestamp | Has_test: 0
Mystatus: Pending Core Response | Has_patch: 0
---------------------------------------+------------------------------------
When running this query (from the sfDoctrineGuardPlugin "remember me" key
functionality):
{{{
// remove old keys
Doctrine_Query::create()
->delete()
->from('sfGuardRememberKey k')
->where('created_at < ?', time() - $expiration_age)
->execute();
}}}
PostgreSQL complains with this error:
{{{
SQLSTATE[22007]: Invalid datetime format: 7 ERROR: invalid input syntax
for type timestamp: "1220955276"
}}}
The error is triggered by:
{{{
at Doctrine_Connection->exec('DELETE FROM sf_guard_remember_key WHERE
created_at < ?', array('1220955276'))
}}}
It seems to work ok when a string is passed, however:
{{{
// remove old keys
Doctrine_Query::create()
->delete()
->from('sfGuardRememberKey k')
->where('created_at < ?', date("Y-m-d", time() - $expiration_age))
->execute();
}}}
The same problem also occurrs if you try to use a timestamp in a fixtures
file - pgsql complains with an error, however if you swap it for a string
representation then it is fine.
This is ok:
{{{
sfGuardUser:
sfGuardUser_1:
username: speiderboy
password: speiderboy
is_active: 1
is_super_admin: 1
created_at: '2008-09-01 11:00:06'
}}}
This is not:
{{{
sfGuardUser:
sfGuardUser_1:
username: speiderboy
password: speiderboy
is_active: 1
is_super_admin: 1
created_at: 1222333976
}}}
Whilst this is not a problem in fixture files really, it may shed some
light on the problem above
The following query works fine in PostgreSQL ( in case it helps)
{{{
DELETE FROM sf_guard_remember_key WHERE created_at < timestamptz 'epoch' +
1220955276 * interval '1 second'
}}}
I wonder if this will work if the act as timestampable behaviour used the
"timestamptz" data type instead of "timestamp"?
I am willing to help/test if I am pointed in the right direction - for now
my code is working because I wrapped the timstamp in a php date function
to keep pgsql happy - but this is surely not the optimal solution?
--
Ticket URL: <http://trac.doctrine-project.org/ticket/1496>
Doctrine <http://www.phpdoctrine.org>
PHP Doctrine Object Relational Mapper
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"doctrine-svn" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.co.uk/group/doctrine-svn?hl=en-GB
-~----------~----~----~----~------~----~------~--~---
