CODEWITHSUNDEEP
sqloracle-database
statquant
statquant

Reputation: 14370

How to get the day from a DateTime

Suppose I have the following Oracle table and I want the last (aka max) entry by aGroup and day where the timepart of dateTime in less than 00:00:15 (day should be extracted as the datepart of dateTime)

What would be the SQL query for it (I do not know how to extract the day from dateTime)?

              dateTime aGroup
2011-01-01 00:00:12      a
2011-01-01 00:00:31      a
2012-01-01 00:00:09      a
2012-01-01 00:00:03      a
2011-01-01 00:00:06      b
2011-01-01 00:00:14      b
2011-01-01 00:00:16      b
2012-01-01 00:00:16      b
2012-01-01 00:00:11      b

This is want I would expect as a result

              dateTime aGroup
2011-01-01 00:00:12      a
2012-01-01 00:00:09      a
2011-01-01 00:00:14      b
2012-01-01 00:00:11      b

Upvotes: 1

Views: 273

Answers (4)

Eric S
Eric S

Reputation: 1363

Consider a query similar to this:

SELECT 
  MAX(DateTime), 
  aGroup
FROM 
  tablename
WHERE 
  TIME_TO_SEC(TIME(DateTime)) < 15
GROUP BY 
  DATE(DateTime), 
  aGroup

This will give you the largest time (less than 00:00:15) for each day for each aGroup.

EDIT: Updated to use TIME_TO_SEC for oracle

Upvotes: 1

Cyril Gandon
Cyril Gandon

Reputation: 17058

The TRUNC function is very handy to extract days :

SELECT dateTime, aGroup
FROM
(
    SELECT dateTime
          , aGroup
          , RANK() OVER (PARTITION BY aGroup, TRUNC(dateTime, 'DDD') ORDER BY dateTime DESC) as Rank
    FROM table
    WHERE (dateTime - TRUNC(dateTime, 'DDD')) * 24 * 3600 < 15
)
WHERE Rank = 1

The line

 WHERE (dateTime - TRUNC(dateTime, 'DDD')) * 24 * 3600 < 15

compute the difference in days between your current date and the start of the day.
Multiply by 24 and 3600 to have the number of seconds, and filter if it is lower than 15.

Upvotes: 1

DazzaL
DazzaL

Reputation: 21973

select dateTime, agroup
  from (select dateTime, agroup, 
               row_number() over (partition by agroup, trunc(datetime)
                                  order by datetime desc) rn
          from yourtab
         where to_number(to_char(datetime, 'hh24miss')) < 15)
 where rn = 1

will do that. see http://sqlfiddle.com/#!4/b4eb98/1

Upvotes: 1

Serkan Arıkuşu
Serkan Arıkuşu

Reputation: 5619

And this is my try using to_char

SELECT AGROUP, MAX(DATETIME) FROM MYTABLE
WHERE TO_CHAR(DATETIME,'SS') <'15'
GROUP BY TO_CHAR(DATETIME,'YYYY-MM-DD'), AGROUP
ORDER BY AGROUP

Upvotes: 1

Related Questions