Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Adding timezone to naive datetime fields from MySQL #331

Closed
robhudson opened this issue Jan 22, 2016 · 3 comments
Closed

Adding timezone to naive datetime fields from MySQL #331

robhudson opened this issue Jan 22, 2016 · 3 comments

Comments

@robhudson
Copy link

@robhudson robhudson commented Jan 22, 2016

Hi,

We have a MySQL database we are migrating to Postgres. All the datetime fields have no timezone and the server is PST. During data migration we would like to have these datetime fields automatically assume a PST timezone during data load. Is this possible?

I've tried a few things that didn't seem to work.

With:
SET client_timezone TO 'PST8PDT'
the timestamps are the exact same time with +00.

With:

 BEFORE LOAD DO
 $$ CREATE SCHEMA IF NOT EXISTS public; $$,
 $$ SET TIMEZONE='PST8PDT'; $$;

the timestamps are also the exact same with +00.

The relevant CAST line in our command file is:

 CAST type datetime to timestamptz drop default drop not null using zero-dates-to-null,

Is there a way to achieve this via pgloader?

Our fallback is to import them without time zone and then do an ALTER TABLE ... USING ... AT TIME ZONE 'PST8PDT' after the fact so Postgres will convert them. I've tested this and this works. But it would be more ideal if we can take care of this during the load.

Thanks

@dimitri
Copy link
Owner

@dimitri dimitri commented Jan 24, 2016

Can you try the following variant spelling:

set timezone to 'PST8PDT'

As a pgloader clause. The SET in the BEFORE LOAD section is going to be run only in that context, and not in the worker threads that actually load the data into PostgreSQL.

@robhudson
Copy link
Author

@robhudson robhudson commented Jan 25, 2016

That was easy. :) That worked exactly how I was expecting. Thanks.

Might this be something that is added to the documentation? I found another github issue asking a similar question.

@robhudson robhudson closed this Jan 25, 2016
@dimitri
Copy link
Owner

@dimitri dimitri commented Jan 25, 2016

Well it's more of a PostgreSQL question really... let's say that I would accept a documentation patch covering it ;-)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
2 participants
You can’t perform that action at this time.