Хранение и поиск нечеткой даты

Я хочу создать базу данных для хранения метаданных о наборе изображений, и одно поле, которое я хочу, - это дата съемки. Я хотел бы иметь возможность хранить это с грубой или мелкозернистой точностью, например. для цифрового изображения будет доступна точная отметка времени с точностью до секунды, но я хотел бы иметь возможность помечать изображение как сделанное в определенном году или даже в определенном десятилетии (десятилетие является самым грубым из них). d go), а также иметь возможность искать таким образом, запрашивая, например, все фотографии 90-х годов, или все фотографии 1992 года, или все фотографии определенного дня и т. д.

Мне было интересно, есть ли встроенный способ сделать это с помощью SQL или есть ли другой способ, который был бы лучше. Я подумал о том, чтобы разбить дату и хранить каждую часть отдельно, например. есть поле десятилетия, поле года, поле месяца и т. д., но это может показаться немного неуклюжим способом ведения дел.

Меня не волнует, какую технологию SQL я использую, пока она бесплатна. Я смотрю на H2 в данный момент.


sql database datetime
person user1111284    schedule 28.07.2012    source источник

Ответы (3)


arrow_upward
3
arrow_downward

Вы можете сделать это всего с двумя столбцами: один для метки времени, а другой для уровня точности. Затем вам нужно определить шкалу точности и некоторый стандарт для кодирования дат с более низкой точностью в отметке времени.

Например, шкала точности может быть:

0   full timestamp
1   day
2   month
3   year
4   decade

При этом вы можете хранить даты следующим образом:

timestamp                 |  precision   | notes
--------------------------+--------------+---------------------
2012-07-05 14:00:00       |  0           | full precision
--------------------------+--------------+---------------------
2012-07-05 00:00:00       |  1           | precision up to day
--------------------------+--------------+---------------------
2012-07-01 00:00:00       |  2           | month and year
--------------------------+--------------+---------------------
2012-01-01 00:00:00       |  3           | year
--------------------------+--------------+---------------------
2010-01-01 00:00:00       |  4           | decade
--------------------------+--------------+---------------------
person bfavaretto    schedule 28.07.2012

arrow_upward
3
arrow_downward

Для нечетких поисков по точным датам вам не нужно хранить каждую часть отдельно. Вы можете настроить предложение where. Для всего с 2012 года:

SELECT * FROM yourtable
WHERE yourtime >= '2012-01-01' AND yourtime < '2013-01-01'

Если вы хотите конкретный день:

SELECT * FROM yourtable
WHERE yourtime >= '2012-07-28' AND yourtime < '2012-07-29'

Или конкретный час:

SELECT * FROM yourtable
WHERE yourtime >= '2012-07-28 13:00:00' AND yourtime < '2012-07-28 14:00:00'

Чтобы сделать все эти запросы эффективными, вы можете добавить индекс в столбец меток времени.

Что касается того, как хранить нечеткие даты, один из вариантов — иметь диапазон дат:

id    taken_from            taken_to               title
1     2011-01-01 00:00:00   2012-01-01 00:00:00    a pic of my car last year

Для нечетких поисков по нечетким датам вы можете сделать что-то вроде этого:

нечеткий поиск по дате

В псевдо-SQL:

SELECT
    *,
    (LEAST(@to, taken_to) - GREATEST(@from, taken_from)) /
    (GREATEST(@to - taken_to) - LEAST(@from, taken_from)) AS relevancy
FROM yourtable
WHERE taken_to >= @from AND taken_from < @to

Вы, вероятно, захотите упорядочить по релевантности, и вы можете включить другие факторы, такие как релевантность, возвращаемая полнотекстовым поиском для некоторых условий поиска.

person Mark Byers    schedule 28.07.2012
comment
Этот подход работает для поиска, если вы точно знаете, когда была сделана фотография, но возможно ли сохранить дату только с годом или только с десятилетием? У меня могут быть фотографии, которые, как я знаю, были сделаны в 1920-х годах, но я не могу сказать точнее, и я не хочу хранить их как сделанные 1 января 1920 года, если это не так. - person user1111284; 29.07.2012
comment
@user1111284: Хммм... вы можете сохранить диапазон дат, в течение которых, по вашему мнению, могла быть сделана фотография, например from - to. Но что делать, если у вас есть фотография, сделанная где-то в 2012 году, и вы ищете все фотографии за июнь 2012 года? Должна ли эта фотография быть включена в поиск или нет? Оно могло быть снято в июне, но, скорее всего, это было не так. Возможно, можно было бы отсортировать по проценту совпадения диапазона на фото и диапазона поиска. - person Mark Byers; 29.07.2012
comment
Да, я не уверен, хочу ли я включать такие элементы или нет. Возможно, они будут в несколько отдельном списке под определенными результатами, упорядоченными по точности. - person user1111284; 29.07.2012

arrow_upward
1
arrow_downward

Раньше я использовал CHAR и VARCHAR, заменяя отсутствующие части вопросительными знаками или тире. Знаки вопроса означали «неизвестно», а тире — «неприменимо». Это оказалось достаточно интуитивно понятным для пользователей (секретарей и помощников юристов в сложных судебных процессах), достаточно гибким для юристов и разумным.

Это означает, что ваши "даты" больше не являются датами SQL. То есть арифметика даты/времени и совместимость интервалов намного менее надежны, если вообще работают. (Что такое «1960-е плюс 20 дней?» Это более длинное десятилетие или сдвинутое десятилетие?) Имеет ли это значение, зависит от приложения. Я не думаю, что это будет проблемой для вашего приложения.

Подробности и предостережения см. на dba.stackexchange.com.

person Mike Sherrill 'Cat Recall'    schedule 28.07.2012