CODEWITHSUNDEEP
sqloracle-databasedatedatetime
Kᴀτᴢ
Kᴀτᴢ

Reputation: 2176

combine date and time and get newest datetime

I have a given oracle table:

enter image description here

The column Datum is DATE and Zeit is VARCHAR2.

I have to combine them to one date and get the max (= newest) date of them. I read it with an oracle reader in c# and combine them but for that I have to load all rows and then search for the max:

DateTime Tempdate = new DateTime(); Tempdate = Convert.ToDateTime(reader.GetValue(0).ToString()); Tempdate = Tempdate.AddHours(Convert.ToDateTime(reader.GetValue(1).ToString()).Hour); Tempdate = Tempdate.AddMinutes(Convert.ToDateTime(reader.GetValue(1).ToString()).Minute); Tempdate = Tempdate.AddSeconds(Convert.ToDateTime(reader.GetValue(1).ToString()).Second);

how can this be done in an sql-statement so that I only get the row with the newest date?

Upvotes: 2

Views: 82

Answers (2)

MT0
MT0

Reputation: 168406

You can use TO_DATE() on the time component:

SELECT datum + ( TO_DATE( zeit, 'HH24:MI:SS' ) - TRUNC( TO_DATE( zeit, 'HH24:MI:SS' ) ) )
FROM   table_name

Then to get the maximum you could use the MAX() on this computed value or, given that the values can be compared using their date/alphanumerical ordering (assuming that the time component does not go over 23:59:59) you could find the maximum first:

SELECT datum,
       zeit
FROM   (
  SELECT datum,
         zeit,
         ROW_NUMBER() OVER ( ORDER BY datum DESC, zeit DESC ) AS rn
  FROM   table_name
)
WHERE  rn = 1

This can use any indexes that you have on the table.

Then use my first query (or @AlexPoole's TO_DSINTERVAL() solution) on this single row.

Upvotes: 3

Alex Poole
Alex Poole

Reputation: 191455

You can use the to_dsinterval() function to add your time string to the date, prepending it with a zero for the number of days (so it's a valid sql_format):

select datum, zeit, datum + to_dsinterval('0 ' || zeit)
from your_table:

DATUM               ZEIT     DATUM+TO_DSINTERVAL('0'||ZEIT)
------------------- -------- ------------------------------
2016-01-13 00:00:00 13:22:30 2016-01-13 13:22:30           
2016-01-13 00:00:00 13:26:30 2016-01-13 13:26:30           
2016-01-13 00:00:00 13:27:30 2016-01-13 13:27:30           
2016-01-13 00:00:00 13:28:30 2016-01-13 13:28:30           
2016-01-13 00:00:00 13:29:30 2016-01-13 13:29:30           
2016-01-13 00:00:00 13:30:30 2016-01-13 13:30:30           
2016-01-13 00:00:00 13:31:30 2016-01-13 13:31:30

And you then just use max() on that calculated value:

select max(datum + to_dsinterval('0 ' || zeit))
from your_table;

MAX(DATUM+TO_DSINTERVAL('0'||ZEIT))
-----------------------------------
2016-01-13 13:31:30

To get the matching row you could do:

select datum, zeit --, other columns
from your_table
where datum + to_dsinterval('0 ' || zeit) = (
  select max(datum + to_dsinterval('0 ' || zeit))
  from your_table
);

though that has to hit the table twice, which you could avoid with a CTE.

You could also use an analytic ranking function, ordering by the date and time:

select datum, zeit,  -- other columns,
  dense_rank() over (order by datum desc, zeit desc) as rnk
from your_table;

DATUM               ZEIT            RNK
------------------- -------- ----------
2016-01-13 00:00:00 13:31:30          1
2016-01-13 00:00:00 13:30:30          2
2016-01-13 00:00:00 13:29:30          3
2016-01-13 00:00:00 13:28:30          4
2016-01-13 00:00:00 13:27:30          5
2016-01-13 00:00:00 13:26:30          6
2016-01-13 00:00:00 13:22:30          7

And pick the row ranked number 1:

select datum, zeit --, other columns
from (
  select t.*, dense_rank() over (order by datum desc, zeit desc) as rnk
  from your_table t
)
where rnk = 1;

DATUM               ZEIT   
------------------- --------
2016-01-13 00:00:00 13:31:30

If you are getting other data from the row as the question suggests, then if you might have two rows with the same date and time you would need to decide how to break ties and pick one of them.

Upvotes: 2

Related Questions