fun with dates when moving from oracle to postgresql

November 29, 2012 — 5 Comments

we are currently in the process of moving an oracle data warehouse to postgresql and had some fun on how oracle handles dates in contrast to postgresql. in oracle, if you subtract one date from another you’ll get an integer:

select to_date('05.01.2012','dd.mm.yyyy') - to_date ( '01.01.2012','dd.mm.yyyy') diff from dual;
      DIFF
----------
         4

if you do the same in postgresql you’ll get an interval:

select to_date('05.01.2012','dd.mm.yyyy') - to_date ( '01.01.2012','dd.mm.yyyy') diff;
  diff
--------
 4 days
(1 row)

the issue was, that a view used this in a where clause and did some calculations based on the return of the expression. obviously this failed when we created the view in postgresql. the trick was to use the to_char function on the interval:

select to_char ( to_date('05.01.2012','dd.mm.yyyy') - to_date ( '01.01.2012','dd.mm.yyyy'), 'DD' ) diff;                                      
diff
------
 04

… we thought :) but the result can still not be used to do some calculations:

select to_char ( to_date('05.01.2012','dd.mm.yyyy') - to_date ( '01.01.2012','dd.mm.yyyy'), 'DD' ) + 8;
ERROR:  operator does not exist: text + integer
LINE 1: ...m.yyyy') - to_date ( '01.01.2012','dd.mm.yyyy'), 'DD' ) + 8;
                                                                   ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

another conversion was necessary to make this work:

select cast ( to_char ( to_date('05.01.2012','dd.mm.yyyy') - to_date ( '01.01.2012','dd.mm.yyyy'), 'DD' ) as int ) + 8;
 ?column?
----------
       12
(1 row)

lessons learned ? oracle is doing implicit type conversions silently in the background. postgresql does not …

another example:

oracle:

SQL> create table t ( a number );
Table created.
SQL> insert into t values ( 20120101 );
1 row created.
SQL>  select to_date(a,'yyyymmdd') from t;
TO_DATE(A,'YYYYMMDD'
--------------------
01-JAN-2012 00:00:00

postgresql:

create table t ( a number );
CREATE TABLE
insert into t values ( 20120101 );
INSERT 0 1
select to_date(a,'yyyymmdd') from t;
ERROR:  function to_date(numeric, unknown) does not exist
LINE 1: select to_date(a,'yyyymmdd') from t;
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
Advertisements

5 responses to fun with dates when moving from oracle to postgresql

  1. 

    date and datetimes are not the funniest part in a migration process from oracle to postgresql. we had much trouble with date-functions at the last migration. i can’t imagine, why every dmbs has own functions. there are lot of differences to mysql and mssql dmbs.

  2. 

    agree. at least learned that postgres is much more exact, which is good from my point of view, because you must know what you are doing and you are much more aware of the data types. implicit type casting for sure makes life easier at the beginning but in the end this might lead to programming errors.
    cheers, daniel

  3. 

    did you figure out how to do your last example?

    create table t ( a number );
    CREATE TABLE
    insert into t values ( 20120101 );
    INSERT 0 1
    select to_date(a,’yyyymmdd’) from t;
    ERROR: function to_date(numeric, unknown) does not exist
    LINE 1: select to_date(a,’yyyymmdd’) from t;
    ^
    HINT: No function matches the given name and argument types. You might need to add explicit type casts.

    • 

      are you looking for this?

      postgres=# select to_date ( cast (a as text),’yyyymmdd’) from t;
      to_date
      ————
      2012-01-01

      cheers
      daniel

      • 

        I think so, I know this is bad code sitting in a view and why on earth would they want to do this, since it is already stored as a date. thanks for your help.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s