Running the following query:
SELECT MAX(timeValue), MIN(timevalue) FROM OurDB.DimTime
Returns the result of:
When this table acutally contains 1440 rows (one row for each minute of the day) and I would expect the result to be:
When I SELECT * FROM OurDB.DimTime ORDER BY timevalue, I get 1440 rows of data in the wrong sort order from 18:00:00 to 17:59:00.
And when I click on the top of the colum to sort the data, it sorts correctly, as expected.
Can anyone comment on the cause of this behavior? Has anyone ever seen it before? I've done a quick google on it and not found an similar answer.
Your session timezone is set to -6, try
SELECT timeValue AT 0 FROM OurDB.DimTime ORDER BY 1
SQL Data Manipulation Language
Chapter 1: The SELECT Statement
ORDER BY Clause
Unexpected Sort Order When Querying DATE Data Type Columns
The AT 0 made it 'right'. So I'll open up the manual and look for options for setting the session variable and maybe even server-level defaults.
(I knew it was something simple, just didn't know how to fix it.)
Also, to add another note incase anyone comes across this topic:
I was able to find a session level setting which fixed this issue:
SET TIME ZONE INTERVAL '00:00'HOUR TO MINUTE;
We are awaiting our DBAs to address this 'feature' at a server level!