All Forums Database
anandc 11 posts Joined 02/13
11 Oct 2013
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

M.Saeed Khurram 544 posts Joined 09/12
11 Oct 2013

Hi, Try the following:

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

 

Khurram

anandc 11 posts Joined 02/13
11 Oct 2013

Thanks for your reply Khurram,
No Luck :( i am still getting '17:00:07' as my minimum

M.Saeed Khurram 544 posts Joined 09/12
11 Oct 2013

Can you please paste some sample data?
 

Khurram

anandc 11 posts Joined 02/13
11 Oct 2013

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

dnoeth 4628 posts Joined 11/04
11 Oct 2013

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

Dieter

anandc 11 posts Joined 02/13
15 Oct 2013

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

You must sign in to leave a comment.