Re: MySQL -> PostgreSQL conversion issue

On 19 Aug 2011, at 14:50, DM wrote:

> Hi All,
>
> how to insert mysql (datetime interval) data to postgrres interval datatype.
>
> mysql
> | test_interval | datetime | YES | | 1970-01-02 00:00:00 | |
>
> psql
> test_interval | interval |
>
>
> any solution for this?

I think you want something like this?:

development=> select '1970-01-02 00:00:00'::timestamp without time zone - '1970-01-01 00:00:00'::timestamp without time zone;
?column?
----------
1 day(1 row)

Midnight 1970-01-01 being the baseline for their calculations is a bit of a guess, but that seems to make sense with the example you gave.
You probably don't want postgres to be smart with your client's time zone settings, hence the "without time zone".

Alban Hertroys

--
Screwing up is an excellent way to attach something to the ceiling.

!DSPAM:737,4e4eadd712091047328385!