Using timestamp with AT TIME ZONE error

[Allen Shen]

Hi there,

When I use the code below:

SELECT t1.timestamp AT TIME ZONE t2.timezone

FROM t1

JOIN t2 ON t1.id = t2.id

I always got "java.lang.NullPointerException".

But the code below works well, I'm not clear how to use TIME ZONE with timestamp.

SELECT t1.timestamp AT TIME ZONE 'America/Chicago'

FROM t1

JOIN t2 ON t1.id = t2.id

Any idea ?

Thanks.

[Yang Yang]

Hi Zhun,

Currently the time zone part of AT TIME ZONE in Presto only accepts a constant string ('America/Chicago') or a constant time interval ('+11:30'). Hope this helps.

Thanks,

Yang

--
You received this message because you are subscribed to the Google Groups "Presto" group.
To unsubscribe from this group and stop receiving emails from it, send an email to presto-users...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Allen Shen]

OK, so I need to find another way to solve it.

Thanks.

You received this message because you are subscribed to a topic in the Google Groups "Presto" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/presto-users/8vt6ipHhsM4/unsubscribe.
To unsubscribe from this group and all its topics, send an email to presto-users...@googlegroups.com.

[Dain Sundstrom]

We could add a function to do this.

-dain

[Allen Shen]

Hi Yang,

It’s my pleasure to help on this.

My environment:

AWS EMR 4.5.0

Presto 0.140

Error log:

2016-04-29T01:48:11.497Z ERROR http-worker-321438 com.facebook.presto.execution.QueryStateMachine Query 20160429_014811_00015_q3epe failed

com.facebook.presto.sql.parser.ParsingException: line 1:139: no viable alternative at input 'TIME ZONE t2'

at com.facebook.presto.sql.parser.SqlParser$1.syntaxError(SqlParser.java:45)

at org.antlr.v4.runtime.ProxyErrorListener.syntaxError(ProxyErrorListener.java:65)

at org.antlr.v4.runtime.Parser.notifyErrorListeners(Parser.java:566)

at org.antlr.v4.runtime.DefaultErrorStrategy.reportNoViableAlternative(DefaultErrorStrategy.java:308)

at org.antlr.v4.runtime.DefaultErrorStrategy.reportError(DefaultErrorStrategy.java:145)

at com.facebook.presto.sql.parser.SqlBaseParser.timeZoneSpecifier(SqlBaseParser.java:7140)

at com.facebook.presto.sql.parser.SqlBaseParser.valueExpression(SqlBaseParser.java:5771)

at com.facebook.presto.sql.parser.SqlBaseParser.predicated(SqlBaseParser.java:5025)

at com.facebook.presto.sql.parser.SqlBaseParser.booleanExpression(SqlBaseParser.java:4913)

at com.facebook.presto.sql.parser.SqlBaseParser.expression(SqlBaseParser.java:4660)

at com.facebook.presto.sql.parser.SqlBaseParser.selectItem(SqlBaseParser.java:3700)

at com.facebook.presto.sql.parser.SqlBaseParser.querySpecification(SqlBaseParser.java:2805)

at com.facebook.presto.sql.parser.SqlBaseParser.queryPrimary(SqlBaseParser.java:2567)

at com.facebook.presto.sql.parser.SqlBaseParser.queryTerm(SqlBaseParser.java:2379)

at com.facebook.presto.sql.parser.SqlBaseParser.queryNoWith(SqlBaseParser.java:2220)

at com.facebook.presto.sql.parser.SqlBaseParser.query(SqlBaseParser.java:1910)

at com.facebook.presto.sql.parser.SqlBaseParser.statement(SqlBaseParser.java:1001)

at com.facebook.presto.sql.parser.SqlBaseParser.singleStatement(SqlBaseParser.java:223)

at com.facebook.presto.sql.parser.SqlParser.invokeParser(SqlParser.java:92)

at com.facebook.presto.sql.parser.SqlParser.createStatement(SqlParser.java:65)

at com.facebook.presto.execution.SqlQueryManager.createQuery(SqlQueryManager.java:277)

at com.facebook.presto.server.StatementResource$Query.<init>(StatementResource.java:286)

at com.facebook.presto.server.StatementResource.createQuery(StatementResource.java:167)

at sun.reflect.GeneratedMethodAccessor426.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:497)

at org.glassfish.jersey.server.model.internal.ResourceMethodInvocationHandlerFactory$1.invoke(ResourceMethodInvocationHandlerFactory.java:81)

at org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher$1.run(AbstractJavaResourceMethodDispatcher.java:151)

at org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher.invoke(AbstractJavaResourceMethodDispatcher.java:171)

at org.glassfish.jersey.server.model.internal.JavaResourceMethodDispatcherProvider$ResponseOutInvoker.doDispatch(JavaResourceMethodDispatcherProvider.java:152)

at org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher.dispatch(AbstractJavaResourceMethodDispatcher.java:104)

at org.glassfish.jersey.server.model.ResourceMethodInvoker.invoke(ResourceMethodInvoker.java:387)

at org.glassfish.jersey.server.model.ResourceMethodInvoker.apply(ResourceMethodInvoker.java:331)

at org.glassfish.jersey.server.model.ResourceMethodInvoker.apply(ResourceMethodInvoker.java:103)

at org.glassfish.jersey.server.ServerRuntime$1.run(ServerRuntime.java:271)

at org.glassfish.jersey.internal.Errors$1.call(Errors.java:271)

at org.glassfish.jersey.internal.Errors$1.call(Errors.java:267)

at org.glassfish.jersey.internal.Errors.process(Errors.java:315)

at org.glassfish.jersey.internal.Errors.process(Errors.java:297)

at org.glassfish.jersey.internal.Errors.process(Errors.java:267)

at org.glassfish.jersey.process.internal.RequestScope.runInScope(RequestScope.java:297)

at org.glassfish.jersey.server.ServerRuntime.process(ServerRuntime.java:254)

at org.glassfish.jersey.server.ApplicationHandler.handle(ApplicationHandler.java:1030)

at org.glassfish.jersey.servlet.WebComponent.service(WebComponent.java:373)

at org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:381)

at org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:344)

at org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:221)

at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:835)

at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1685)

at org.eclipse.jetty.servlets.GzipFilter.doFilter(GzipFilter.java:51)

at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1668)

at io.airlift.http.server.TraceTokenFilter.doFilter(TraceTokenFilter.java:63)

at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1668)

at io.airlift.http.server.TimingFilter.doFilter(TimingFilter.java:52)

at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1668)

at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:581)

at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1160)

at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:511)

at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1092)

at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141)

at org.eclipse.jetty.server.handler.HandlerCollection.handle(HandlerCollection.java:119)

at org.eclipse.jetty.server.handler.StatisticsHandler.handle(StatisticsHandler.java:169)

at org.eclipse.jetty.server.handler.HandlerList.handle(HandlerList.java:52)

at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:134)

at org.eclipse.jetty.server.Server.handle(Server.java:517)

at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:308)

at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:244)

at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:273)

at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:95)

at org.eclipse.jetty.io.SelectChannelEndPoint$2.run(SelectChannelEndPoint.java:93)

at org.eclipse.jetty.util.thread.strategy.ExecuteProduceConsume.produceAndRun(ExecuteProduceConsume.java:246)

at org.eclipse.jetty.util.thread.strategy.ExecuteProduceConsume.run(ExecuteProduceConsume.java:156)

at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:654)

at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:572)

at java.lang.Thread.run(Thread.java:745)

Caused by: org.antlr.v4.runtime.NoViableAltException

at org.antlr.v4.runtime.atn.ParserATNSimulator.noViableAlt(ParserATNSimulator.java:1886)

at org.antlr.v4.runtime.atn.ParserATNSimulator.execATN(ParserATNSimulator.java:486)

at org.antlr.v4.runtime.atn.ParserATNSimulator.adaptivePredict(ParserATNSimulator.java:412)

at com.facebook.presto.sql.parser.SqlBaseParser.timeZoneSpecifier(SqlBaseParser.java:7111)

... 69 more

2016-04-29T01:48:11.498Z WARN http-worker-321438 com.facebook.presto.server.ThrowableMapper Request failed for /v1/statement

java.lang.NullPointerException

On Apr 29, 2016, at 6:56 AM, Yang Yang <gerai...@gmail.com> wrote:

Hi Zhun,

I am a developer in Presto team, may I ask for a full stack trace when you type:

SELECT t1.timestamp AT TIME ZONE t2.timezone

FROM t1

JOIN t2 ON t1.id = t2.id

in your Presto?

Event Presto doesn't accept a variable in time zone part, the 'NullPointerException' is not expected. I would like to fix this issue.

I will appreciate if you could give some help on this. Thank you very much.

Best Regards,

Yang Yang

On Wed, Apr 27, 2016 at 11:26 PM Zhun Shen <shenzh...@gmail.com> wrote:

[Allen Shen]

Great! I think it’s will be an important function for timestamp. We need to calculate different users with their timezone in lots of case.

[Shanmuga sundaram Swaminathan]

Any Updates?