The forum will be unavailable for maintenance at some point from Friday, April 13 at 19:00 EDT until Sunday, April 15 at 23:59 EDT. Downtime will be minimized but the exact timing is unknown.

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 ==

asked 01 Feb '12, 21:35

Erik%20Anderson's gravatar image

Erik Anderson
accept rate: 15%

This issue was fixed last November in

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


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%20Culp's gravatar image

Mark Culp
accept rate: 40%

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
Your answer
toggle preview

Follow this question

By Email:

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



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "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

Question tags:


question asked: 01 Feb '12, 21:35

question was seen: 1,442 times

last updated: 01 Feb '12, 23:32