Обсуждение: MySQL -> PostgreSQL conversion issue

On 8/19/2011 7:50 AM, 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?
>
> thanks
> Deepak

Well.. What is the interval in mysql?  Is that supposed to represent +
interval '1970 years' + interval '1 month' + interval '2 days'?

In PG you probably have a few options.  One, store it as a date, and use
dateAdd() functions.  Two, store it as text and set the value's to
things like '1 hour', '2 years', '70 years 1 month 2 days 4 hours 20
minutes', etc

The calc like 'select someDate + interval yourNewField'

Of course... I have not actually tried any of this :-)

-Andy

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
theexample 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!