SELECT MAX(timeValue), MIN(timevalue) FROM OurDB.DimTime - Returns wrong result

Database

SELECT MAX(timeValue), MIN(timevalue) FROM OurDB.DimTime - Returns wrong result

Running the following query:

SELECT MAX(timeValue), MIN(timevalue) FROM OurDB.DimTime

 Returns the result of:

Maximum(TimeValue) Minimum(TimeValue)
17:59:00.000000  18:00:00.000000

When this table acutally contains 1440 rows (one row for each minute of the day) and I would expect the result to be:

Maximum(TimeValue) Minimum(TimeValue)
23:59:00.000000  00:00:00.000000

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.

Row    TimeValue
0001  18:00:00.000000
0002  18:01:00.000000
0003  18:02:00.000000
0004  18:03:00.000000
0005  18:04:00.000000
...
0358  23:57:00.000000
0359  23:58:00.000000
0360  23:59:00.000000
0361  00:00:00.000000
0362  00:01:00.000000
0363  00:02:00.000000
0364  00:03:00.000000
...
1437  17:56:00.000000
1438  17:57:00.000000
1439  17:58:00.000000
1440  17:59:00.000000

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.

Thanks!

3 REPLIES
Junior Contributor

Re: SELECT MAX(timeValue), MIN(timevalue) FROM OurDB.DimTime - Returns wrong result

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

Re: SELECT MAX(timeValue), MIN(timevalue) FROM OurDB.DimTime - Returns wrong result

Thank you!

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.)

Re: SELECT MAX(timeValue), MIN(timevalue) FROM OurDB.DimTime - Returns wrong result

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!