Блог

#sql #oracle #date #oracle11g

Вопрос:

  SELECT datadate
 FROM mytable
 WHERE trailerid= '1'  and datattime>'05:00:00' and datattime<'12:00:00'
 AND datadate between '18/05/2021'  and '28/05/2021'
 GROUP BY datadate ORDER BY datadate;
 

 
DATADATE
----------------
18/05/2021
19/04/2021
19/05/2021
20/05/2021
21/04/2021
21/05/2021
22/04/2021
22/05/2021
23/04/2021
23/05/2021
24/04/2021
24/05/2021
25/04/2021
25/05/2021
26/04/2021
26/05/2021
27/04/2021
27/05/2021
28/04/2021
28/05/2021
 

Комментарии:

1. Даты в этом формате не упорядочены, потому что для сравнения дат вам нужно сначала сравнить годы, затем месяцы, затем дни. Порядок этих частей в вашей строке отличается

2. Я не знаю, где я ошибаюсь — примерно в той части, где вы сказали: даты были сохранены в базе данных с типом данных varchar — нет, нет, нет, нет, нет. Исправьте эту фундаментальную проблему, и эта последующая проблема исчезнет навсегда

3. Эй, спасибо, причина, по которой я хотел попробовать это, заключается в том, что в этой колонке у меня были огромные куски данных. Теперь, похоже, мне придется начинать все сначала. Спасибо, что ответили

4. Если вы собираетесь придерживаться хранения дат в виде строк, по крайней мере, храните их в гггг-мм-дд, чтобы МЕЖДУ работами.. Вы жалуетесь, что BETWEEN '18/05..' AND '28/05..' возвращает 04 даты, но строка 19/04.. действительно находится между ними по той же причине, по которой «M/A» находится между «L/Y» и «Z/Z», хотя A не находится между Y и Z! Честно говоря, потребуется около 5 минут, чтобы добавить еще один столбец типа datetime, скопировать в него данные, удалить старый столбец и переименовать новый обратно в старый. Кроме того, бонус, ваш новый блестящий столбец DATETIME также может хранить содержимое datatime.

5. Сохранение дат в виде строки также приведет к искажению статистики оптимизатора, что может привести к плохим планам выполнения

Ответ №1:

  AND to_date(datadate, 'dd/mm/yyyy') between to_date('18/05/2021', 'dd/mm/yyyy')  
                                         and to_date('28/05/2021', 'dd/mm/yyyy')
 

Комментарии:

1. Эй, спасибо за ваш ответ, но он показывает ошибку 🙁 Это неверное совпадение

2. Именно так, как я сказал — вот что происходит, когда люди хранят значения в виде строк. Некоторые значения в этом столбце не соответствуют формату даты, и Oracle выдает ошибку. Вы ничего не можете с этим поделать, кроме как найти и исправить недопустимые значения. Простой, но неэффективный способ сделать это-использовать цикл и хранить (или отображать) недопустимые строки, чтобы впоследствии их можно было исправить.

3. Ты тоже хотел завернуться datadate внутрь to_date ? Я думал, что в этом весь смысл твоего ответа.

4. Абсолютно, @mathguy. Спасибо. Я должен стараться не делать несколько вещей одновременно, результат действительно плохой 🙁

Ответ №2:

 ALTER TABLE t ADD x DATE;

UPDATE t SET x = to_date(concat(datadate,datatime), 'dd/mm/yyyyhh24:mi:ss'))
WHERE datadate in (SELECT to_char(to_date('1999-12-31', 'yyyy-mm-dd')   level, 'dd/mm/yyyy') FROM dual CONNECT BY level <= 10000)
 

 SELECT * FROM t WHERE x is null
 

Комментарии:

1. Хотя ваш общий подход обоснован, в oracle нет типа данных DATETIME. Правильный тип данных для этого приложения-ДАТА (ИЗМЕНИТЬ ТАБЛИЦУ, ДОБАВИТЬ ДАТУ x;)

2. Сделано обновление, спасибо Эд