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

Thank you for your reply, sorry for the late response, your reply really
helped me.

Thanks
Deepak

On Fri, Aug 19, 2011 at 11:39 AM, Alban Hertroys <
dalroi@solfertje.student.utwente.nl> wrote: