Derby/PostgreSQL/MySQL cross-db compatible way to extract date information?

[Axeia]

Bleh this is driving me nuts, silly assignment where I have to use the 3 databases in the subjectline without a database abstraction layer.

Dates in particular are annoying.

EXTRACT( 'YEAR', date ) works with postgresql and mysql but not with derby

YEAR( date ) works with derby and mysql, but not with postgresql

to_char( 'yyyy', date ) only works with postgresql

 

So besides a regular expression to replace YEAR( DATE ) with EXTRACT( 'YEAR', date ).. any other options?

 

[edit]

Oh right, it's not just years, also months/days.

[Axeia]

Okay deadline passed. Still interested if anyone knows a way though.

 

I ended up using YEAR( date ) etc as both Derby and MySQL understand this and postgresql will get the following from a function

$q = str_replace( "YEAR( ", "EXTRACT( YEAR FROM", $q );

[artacus]

Just in case you are still interested. This will add the mysql style date() to postgres. Its better than doing string replaces.

 

CREATE OR REPLACE FUNCTION year(date) 
RETURNS int AS
$$
   SELECT EXTRACT(YEAR FROM $1)::int
$$ LANGUAGE 'sql' IMMUTABLE STRICT;

[Axeia]

That's nifty, is that a one time per connection thing? (Can't really test atm as I don't have postgresql running)

[artacus]

No, its a one time per database thing. It will create the function on your db and you'd call it just like you do the built in functions in mysql or derby.