Minmum Time field selection - TIME(6)

Database
Enthusiast

Minmum Time field selection - TIME(6)

Dear All,

I have a Table where i have 2 fields - date_col and time_col.

DATE_COL - DATE FORMAT 'YY/MM/DD'

TIME_COL - TIME(6)

I am trying to select the min(time_col) for a particular date / current_date...

When i select all the records from the table for a particular date, i can see the min of time_col is '00:01:33'

But when i run a query saying

SELECT MIN(TIME_COL) FROM TABLE_NAME WHERE DATE_COL = 'current_date'

My results is showing me '17:00:07'

I tried converting the time to different formats and adding it to date field so i can check it as one value, but my result always comes back with '17:00:07' as the min rather '00:01:33'

Please help me understand what am i doing wrong here.. Please help me with the correct query to get me the exact min time for a particular date.. I am using this min time to process all the records for this particular date and join with another source table to fetch the records.. Unfortunately this is a source table where i am not able to change the structure to have one column (TIMESTAMP(6))

I am not sure if this is a ODBC connection problem or SQLA showing the results as this way...

any help would be greatly appriciated

Thanks !

Anand

6 REPLIES
Enthusiast

Re: Minmum Time field selection - TIME(6)

Hi, Try the following:

SELECT MIN(TIME_COL) OVER(Partition by Date_Col Order By Time_Col)
FROM TABLE_NAME;
Khurram
Enthusiast

Re: Minmum Time field selection - TIME(6)

Thanks for your reply Khurram,

No Luck :( i am still getting '17:00:07' as my minimum

Enthusiast

Re: Minmum Time field selection - TIME(6)

Can you please paste some sample data?

Khurram
Enthusiast

Re: Minmum Time field selection - TIME(6)

Here are some sample data...





act_dt act_tm
2013-06-01 00:01:33
2013-06-01 00:02:33
2013-06-01 00:15:22
2013-06-01 03:43:35
2013-06-01 05:06:51
2013-06-01 06:28:23
2013-06-01 07:09:30
2013-06-01 08:23:29
2013-06-01 10:08:43
2013-06-01 16:37:41
2013-06-01 17:00:07
2013-06-01 17:00:33
2013-06-01 17:09:59
2013-06-01 21:02:29
2013-06-01 22:18:18
2013-06-01 22:19:09
2013-06-01 23:05:33
2013-06-01 23:40:39
2013-06-01 23:48:32

I even tried to run this but no luck... its always giving me 17:00:07

SELECT MIN(act_tm) FROM TABLENAME

WHERE act_dt IN (

SELECT MIN(act_dt) FROM TABLENAME

WHERE act_dt BETWEEN'2013-06-01' AND '2013-08-01'

)

Junior Contributor

Re: Minmum Time field selection - TIME(6)

Do an ORDER BY act_tm and you'll see what happened :-)

TIME is internally stored as UTC and sorted based on this, MIN returns the minimum value based on this order and your session timezone is probably set to -7.

SELECT min(cast(act_tm as char(8))) should return '00:01:33'.

SQL Data Manipulation Language

Chapter 1: The SELECT Statement

ORDER BY Clause

Unexpected Sort Order When Querying DATE Data Type Columns

Dieter

Enthusiast

Re: Minmum Time field selection - TIME(6)

Thats exactly whats happening Dieter :) thanks a ton for your reply.. Really appriciate it