Name is required.
Email address is required.
Invalid email address
Answer is required.
Exceeding max length of 5KB

start at - timezone information

In my start component I am exporting "started to" a timestamp variable. Subsequently in an API component in Advanced mode my SQL Query has:

... cast('${timestamp}' as timestamp) as thetime ...

the result is I get the timestamp as the UTC time, but no timezone information is stored.

Now, I could do this:

convert_timezone('UTC', 'America/Los_Angeles', thetime)

However, is there a way to the data stored in my timezone?

3 Community Answers

Matillion Agent  

Paul Johnson —

Hi Quinn,
From what I can tell you are using Matillion for Snowflake.

You can store timestamps either as UTC, without a timezone or with a timezone offset. For more info see here

For example to take the “wall clock” time, with no time zone you would simply change your above SQL query to
cast(‘${timestamp}’ as TIMESTAMP_NTZ) as thetime

You may also consider using a snowflake session parameter such as TIMESTAMP_LTZ_OUTPUT_FORMAT
which displays all timestamps in your local timezone.

Regards,
Paul


Quinn Wildman —

Yes, I am using snowflake.

I know about the different snowflake types. However, because my data is being created as a result of an API Query, I don't see any way to control the type - that is why I asked my question. So, in my API Query, I'd love to be able to say:

cast('${timestamp}' as TIMESTAMP_LTZ) as thetime

or

cast('${timestamp}' as TIMESTAMP_TZ) as thetime

However, those generate syntax errors.

So, my question remains. How do I get my results in local time or stored with a timezone?


Matillion Agent  

Paul Johnson —

Hi Quinn,
You did mention it was an API query in the original post, my apologies.
As the timestamp starts off as a Matillion generated variable, you would need to change the timezone on your instance. To do that please follow:

1. SSH to the matillion instance
2. Edit the file /etc/tomcat8/tomcat8.conf
3. Add the following line at the end: JAVA_OPTS=“$JAVA_OPTS -Duser.timezone=America/Los_Angeles”
4. Restart tomcat (sudo service tomcat8 restart)

Regards,
Paul

Post Your Community Answer

To add an answer please login