PDO and date(time)s

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
Hi all,

Until now I have always used adodb for database abstraction.
(http://www.phplens.com/adodb /)
Or database specific PHP functions.
I thought it might be good/fun to try PDO for my next project.
PDO doesn't offer full database abstraction, just 'database access
abstraction', according to the specs on www.php.net.

So far, everything in PDO seems to make sense, works as expected, etc,
except: Where is the date and/or datetime handling?
It is simply not implemented at all it seems.

Since I am new to PDO and nobody ever used the words "PDO datetime" ever
before in comp.lang.php according to googlegroups, I thought I better ask.
(But I don't trust google groups too much these days)

I created a simple example so we have something to talk about:

** database: **
(Postgres, but that doesn't matter too much)

create table tbldob(
   dob_id serial primary key,
   name text,
   dob date

Now suppose we want to store a name and the dob (dob = date of birth).
Posted ($_POST) is:
$_POST["name"] a string
$_POST["dob_year"] , expecting an integer
$_POST["dob_month"] , expecting an integer
$_POST["dob_day"] , expecting an integer

Here under follows the code with three approaches I could think of.
I don't like any so far.
Please comment. :-)

** PHP/PDO ** (errorhandling stuff removed)
$connection = new PDO($dsn, $dbusername, $dbpassword);
$connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);

Approach 1: Use multiple placeholders
$prepStm = $connection->prepare
("INSERT INTO tbldob (name,dob) VALUES (:name,
date(:dob_year || '-' || :dob_month || '-' || :dob_day));");


Approach 2: Assemble the date string by hand:
$prepStm = $connection->prepare
("INSERT INTO tbldob (name,dob) VALUES (:name, date(:dob));");

$dob = $_POST["dob_year"]."-".$_POST["dob_month"]."-".$_POST["dob_day"];

Approach 3: Cast to UTS and use UTS on database too

My conclusion:

My problem with my first 2 approaches is that I am constructing a string
for a date that I know will be understood by Postgres.
(And I also use date() function, which is maybe postgres-only too.)
But MS Access, for example, uses something like #Y-m-d# for dates and
#Y-m-d h:i:sA# for timestamps.

So that doesn't seem a good approach to real "data-access abstraction"
at all.

Does that mean I should use Unix timestamps (approach3) to do it right?

Is it just me, or is this a missing piece of functionality?
How do others use PDO when it comes to dates and datetimes?

If you have experience with PDO, please let me hear what you think.
Thanks for your time.

Erwin Moller

"There are two ways of constructing a software design: One way is to
make it so simple that there are obviously no deficiencies, and the
other way is to make it so complicated that there are no obvious
deficiencies. The first method is far more difficult."
-- C.A.R. Hoare

Re: PDO and date(time)s

"Erwin Moller"
Quoted text here. Click to load it

I get your frustration, but I think at the root of it, you're talking about
a difference in SQL, not access.  IF one RDBMS prefers/requires one date
format, and another prefers something else, there's nothing in PDO that will
help you build syntactically valid SQL for that.

By the same token, there's nothing in PDO that will be able to assist with
the correct syntax for row-limiting between MySQL, MSSQL, Oracle, etc.
(LIMIT, TOP, etc.)

I think what you're really after here is a SQL-abstraction layer.  I've
built my own just for this purpose.

Site Timeline