Fully managed cloud. 30-day FREE full featured trial. Start Now
cancel
Showing results for 
Search instead for 
Did you mean: 

Timezone conversion in LUA script

mskempri
Contributor

I'm using a LUA function to pull data from a mysql database. 

MySQL database operates in UTC and Exasol Server is on America/New York . The function uses the mysql function from_unixtime() to convert from utc unixtime to utc datetime format but it looks like when I pull from the UDF, the datetime is already converted to the server timezone when I really I need to be storing UTC timestamps. 

Is there a way that I can set outside my function or without hardcoding the timezone conversion (as it is a function that is used in multiple environments) to make sure that the timezone is honored?

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions

wunderdata
SQL-Fighter

Hi,

do you use a new JDBC-Connection To MySQL int he UDF? You could then overwrite the server-timezone with the JDBC-param to your own timezone, thereby preventing the conversion:

&serverTimezone=<YOURTIMEZONE>
Then all you would need to do is, to query the local timezone in same function and use it in there, so the function works in any timezone then.

View solution in original post

7 REPLIES 7

exa-MathiasHo
Exasol Alumni

Hi @mskempri

Thank you for posting. Even though I can't answer your question myself, I will nudge internally to find a solution for you! Please bear with us.

exa-Kristof
Team Exasol
Team Exasol

Hi @mskempri ,

have you tried altering your session to UTC timezone before executing your function?

 

alter session set TIME_ZONE = 'UTC';

 

 

Also, we have a datatype "TIMESTAMP WITH LOCAL TIME ZONE" which internally normalizes the timestamp to UTC. See https://docs.exasol.com/sql_references/data_types/datatypedetails.htm#DateTime for more details.

I've done some tests to showcase the difference between "TIMESTAMP" and "TIMESTAMP WITH LOCAL TIME ZONE" and I think that those two things should be able to help you out here:

 

CREATE TABLE TEST.TEST_TIMEZONE(TS TIMESTAMP, TS_LOCAL_TZ TIMESTAMP WITH LOCAL TIME ZONE);

SELECT * FROM EXA_PARAMETERS WHERE PARAMETER_NAME = 'TIME_ZONE';
--PARAMETER_NAME SESSION_VALUE SYSTEM_VALUE
--TIME_ZONE      EUROPE/BERLIN EUROPE/BERLIN

INSERT INTO TEST.TEST_TIMEZONE VALUES ('2021-12-09 20:00:00', '2021-12-09 20:00:00');

Select * from TEST.TEST_TIMEZONE;
--TS                  TS_LOCAL_TZ
--2021-12-09 20:00:00 2021-12-09 20:00:00

alter session set TIME_ZONE = 'UTC';

Select * from TEST.TEST_TIMEZONE;
--TS                  TS_LOCAL_TZ
--2021-12-09 20:00:00 2021-12-09 19:00:00

 

 

If that does not solve your problem maybe you can post some code and value examples.

Best regards

wunderdata
SQL-Fighter

Hi,

do you use a new JDBC-Connection To MySQL int he UDF? You could then overwrite the server-timezone with the JDBC-param to your own timezone, thereby preventing the conversion:

&serverTimezone=<YOURTIMEZONE>
Then all you would need to do is, to query the local timezone in same function and use it in there, so the function works in any timezone then.

mskempri
Contributor

Oh this is interesting. I have set up a connection on the server rather than use a connection in the UDF - Is there a way of setting the timezone when setting up the connection between the servers?

mskempri
Contributor

Kristof, Thank you for this, I however had no luck with this, other than to discover that what showed in exa_operation as America/New_York in the system showed Europe/London. I'm working with my sysadmin to see how was that set up in the beginning.

I changed the system timezone variable to America/New York and I saw the problem persist.  

mskempri
Contributor

Thank you both for your help! I changed the connection string to include the exasol server timezone like so:

 

CREATE CONNECTION "DB_CONN_NEW" to 
'jdbc:mysql://servername/dbname?serverTimezone=America/New_York' 
USER 'username' IDENTIFIED BY 'password' ;

 

 

And I can see the timestamps are coming through as expected. 

wunderdata
SQL-Fighter

Hi glad this worked! One more thing you should probably add to is zeroDateTimeBehavior=convertToNull

'jdbc:mysql://servername/dbname?serverTimezone=America/New_York&zeroDateTimeBehavior=convertToNull' 

 

Because in some MySQL Null-Dates are stored like 0000-00-00 00:00:00 which will give you errors when importing in Exasol.