Aggregate functions not allowed for TIMESTAMP WITH TIME ZONE ?

I've been slowly exploring adapting TIMESTAMP WITH TIME ZONE with our product and tonight did an initial test ALTERing a number of existing TIMESTAMP columns over. Our product immediately failed with the following error:

Cannot convert timestamp with time zone to a numeric

The SQL behind it appears to be: "select max(LastChanged) from dba.LocalSettings"

This command succeeds if I substitute "cast(LastChanged as timestamp)" but that kinda defeats the purpose. Is this intentional? The help files seem rather silent on how operations that succeed on timestamps would fail on timestamp-with-time-zone, some places even suggest that it would degrade to a simple timestamp when necessary.

If timestamp-with-time-zone is such a different animal from simple-timestamp then this is going to be a much more painful migration...

select @@version == 12.0.1.3406

asked 01 Feb '12, 21:35

Erik Anderson
436●8●13●23
accept rate: 15%

flat view

One Answer:

active answers oldest answers newest answers popular answers

This issue was fixed last November in 12.0.1.3504

QTS 690734 : The MIN/MAX aggregates did not support TIMESTAMP WITH TIME ZONE values, XML, or varbit.

Previously, the MIN and MAX aggregate functions would return an error when using an argument of type "timestamp with time zone" or "xml":

Cannot convert timestamp with time zone to a numeric

or

Cannot convert xml to a numeric

Further, using an argument of type varbit (or long varbit) would convert the argument to type numeric before computing the aggregate.

This has been fixed. The MIN and MAX aggregates no longer attempt to cast these types to numeric.

You will need to download and apply a recent EBF.

permanent link

answered 01 Feb '12, 23:27

Mark Culp
24.9k●10●139●297
accept rate: 41%

figures, thanks for the fast grep of the ChangeLog. Will try to do something later this week, hopefully I can disable enough stuff prevent a forced reboot.

(01 Feb '12, 23:32) Erik Anderson

flat view

Your answer

toggle preview

community wiki:

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

*italic* or _italic_
**bold** or __bold__
link:[text](http://url.com/ "title")
image?![alt text](/path/img.jpg "title")
numbered list: 1. Foo 2. Bar
to add a line break simply add two spaces to where you would like the new line to be.
basic HTML tags are also supported

learn more about Markdown

Question tags:

sql ×90
error-message ×43
timestamp ×22
aggregate ×5

question asked: 01 Feb '12, 21:35

question was seen: 2,769 times

last updated: 01 Feb '12, 23:32

SAP SQL Anywhere

Aggregate functions not allowed for TIMESTAMP WITH TIME ZONE ?

Follow this question

Related questions