Статьи

Вы, скорее всего, получили арифметику SQL даты и времени неверно!

Январь 9, 2019

Вы, скорее всего, ошиблись в арифметике даты и времени SQL. И почему так? Поищи в Гугле! Вы быстро найдете сообщения в блоге, подобные этим:

И все они защищают такие вещи:

SYSDATE + (10/1440) через десять минут.

Это правда? Как насчет этой красоты:

1
2
3
4
5
6
7
SELECT TO_CHAR(hiredate,'DD.MM.YYYY:HH24:MI:SS') "Hiredate",
       TO_CHAR(&Today,'DD.MM.YYYY:HH24:MI:SS') "Today",
       trunc(86400*(&Today-hiredate))-60*(trunc((86400*(&&Today-hiredate))/60)) "Sec",
       trunc((86400*(&Today-hiredate))/60)-60*(trunc(((86400*(&&Today-hiredate))/60)/60)) "Min",
       trunc(((86400*(&Today-hiredate))/60)/60)-24*(trunc((((86400*(&&Today-hiredate))/60)/60)/24)) "Hrs",
       trunc((((86400*(&Today-hiredate))/60)/60)/24) "Days"
FROM emp;

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

Но, к счастью, есть jOOQ для стандартизации SQL. Мы уже писали о стандартизации выражений и делаем это снова, с помощью этой простой программы арифметического тестирования даты и времени:

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
import static org.jooq.DatePart.DAY;
import static org.jooq.DatePart.HOUR;
import static org.jooq.DatePart.MINUTE;
import static org.jooq.DatePart.MONTH;
import static org.jooq.DatePart.SECOND;
import static org.jooq.DatePart.YEAR;
import static org.jooq.SQLDialect.INGRES;
import static org.jooq.SQLDialect.SQL99;
import static org.jooq.impl.DSL.select;
import static org.jooq.impl.DSL.timestampAdd;
import static org.jooq.impl.DSL.using;
 
import java.sql.Timestamp;
import java.util.EnumSet;
 
import org.jooq.QueryPart;
import org.jooq.SQLDialect;
import org.jooq.conf.Settings;
 
public class Compatibility {
 
    public static void main(String[] args) {
        Timestamp t = new Timestamp(0);
 
        // Using the new version of the
        // timestampAdd() function
        // that will be added in jOOQ 3.3
        print(select(
            timestampAdd(t, 2, YEAR)  .as("yy"),
            timestampAdd(t, 2, MONTH) .as("mm"),
            timestampAdd(t, 2, DAY)   .as("dd"),
            timestampAdd(t, 2, HOUR)  .as("hh"),
            timestampAdd(t, 2, MINUTE).as("mi"),
            timestampAdd(t, 2, SECOND).as("ss")
        ));
    }
 
    private static void print(QueryPart part) {
        System.out.println("Printing " + part);
        System.out.println("---------------------");
 
        // Get only SQLDialect families, don't
        // distinguish between
        // SQL Server 2008 or SQL Server 20012
        EnumSet<SQLDialect> dialects =
            EnumSet.noneOf(SQLDialect.class);
        for (SQLDialect dialect:SQLDialect.values())
            if (dialect != SQL99 && dialect != INGRES)
                dialects.add(dialect.family());
 
        // Render the passed in SQL clause to
        // all supported SQL dialects
        for (SQLDialect dialect: dialects)
            System.out.println(
                String.format("%1$s: \n%2$s\n",
                dialect, using(dialect, new Settings()
                         .withRenderFormatted(true))
                         .renderInlined(part)
            ));
 
        System.out.println();
        System.out.println();
    }
}

А вот вывод для большинства баз данных, в настоящее время поддерживаемых jOOQ:

001
002
003
004
005
006
007
008
009
010
011
012
013
014
015
016
017
018
019
020
021
022
023
024
025
026
027
028
029
030
031
032
033
034
035
036
037
038
039
040
041
042
043
044
045
046
047
048
049
050
051
052
053
054
055
056
057
058
059
060
061
062
063
064
065
066
067
068
069
070
071
072
073
074
075
076
077
078
079
080
081
082
083
084
085
086
087
088
089
090
091
092
093
094
095
096
097
098
099
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
-- CUBRID:
select
  date_add(datetime '1970-01-01 01:00:00.0', interval 2 year) "yy",
  date_add(datetime '1970-01-01 01:00:00.0', interval 2 month) "mm",
  date_add(datetime '1970-01-01 01:00:00.0', interval 2 day) "dd",
  date_add(datetime '1970-01-01 01:00:00.0', interval 2 hour) "hh",
  date_add(datetime '1970-01-01 01:00:00.0', interval 2 minute) "mi",
  date_add(datetime '1970-01-01 01:00:00.0', interval 2 second) "ss"
from "db_root"
 
-- DERBY:
select
  {fn timestampadd(sql_tsi_year, 2, timestamp('1970-01-01 01:00:00.0')) } as "yy",
  {fn timestampadd(sql_tsi_month, 2, timestamp('1970-01-01 01:00:00.0')) } as "mm",
  {fn timestampadd(sql_tsi_day, 2, timestamp('1970-01-01 01:00:00.0')) } as "dd",
  {fn timestampadd(sql_tsi_hour, 2, timestamp('1970-01-01 01:00:00.0')) } as "hh",
  {fn timestampadd(sql_tsi_minute, 2, timestamp('1970-01-01 01:00:00.0')) } as "mi",
  {fn timestampadd(sql_tsi_second, 2, timestamp('1970-01-01 01:00:00.0')) } as "ss"
from "SYSIBM"."SYSDUMMY1"
 
-- FIREBIRD:
select
  dateadd(year, 2, timestamp '1970-01-01 01:00:00.0') "yy",
  dateadd(month, 2, timestamp '1970-01-01 01:00:00.0') "mm",
  dateadd(day, 2, timestamp '1970-01-01 01:00:00.0') "dd",
  dateadd(hour, 2, timestamp '1970-01-01 01:00:00.0') "hh",
  dateadd(minute, 2, timestamp '1970-01-01 01:00:00.0') "mi",
  dateadd(second, 2, timestamp '1970-01-01 01:00:00.0') "ss"
from "RDB$DATABASE"
 
-- H2:
select
  dateadd('year', 2, timestamp '1970-01-01 01:00:00.0') "yy",
  dateadd('month', 2, timestamp '1970-01-01 01:00:00.0') "mm",
  dateadd('day', 2, timestamp '1970-01-01 01:00:00.0') "dd",
  dateadd('hour', 2, timestamp '1970-01-01 01:00:00.0') "hh",
  dateadd('minute', 2, timestamp '1970-01-01 01:00:00.0') "mi",
  dateadd('second', 2, timestamp '1970-01-01 01:00:00.0') "ss"
from dual
 
-- HSQLDB:
select
  {fn timestampadd(sql_tsi_year, 2, timestamp '1970-01-01 01:00:00.0') } as "yy",
  {fn timestampadd(sql_tsi_month, 2, timestamp '1970-01-01 01:00:00.0') } as "mm",
  {fn timestampadd(sql_tsi_day, 2, timestamp '1970-01-01 01:00:00.0') } as "dd",
  {fn timestampadd(sql_tsi_hour, 2, timestamp '1970-01-01 01:00:00.0') } as "hh",
  {fn timestampadd(sql_tsi_minute, 2, timestamp '1970-01-01 01:00:00.0') } as "mi",
  {fn timestampadd(sql_tsi_second, 2, timestamp '1970-01-01 01:00:00.0') } as "ss"
from "INFORMATION_SCHEMA"."SYSTEM_USERS"
 
-- MARIADB:
select
  date_add(timestamp '1970-01-01 01:00:00.0', interval 2 year) as `yy`,
  date_add(timestamp '1970-01-01 01:00:00.0', interval 2 month) as `mm`,
  date_add(timestamp '1970-01-01 01:00:00.0', interval 2 day) as `dd`,
  date_add(timestamp '1970-01-01 01:00:00.0', interval 2 hour) as `hh`,
  date_add(timestamp '1970-01-01 01:00:00.0', interval 2 minute) as `mi`,
  date_add(timestamp '1970-01-01 01:00:00.0', interval 2 second) as `ss`
from dual
 
-- MYSQL:
select
  date_add(timestamp '1970-01-01 01:00:00.0', interval 2 year) as `yy`,
  date_add(timestamp '1970-01-01 01:00:00.0', interval 2 month) as `mm`,
  date_add(timestamp '1970-01-01 01:00:00.0', interval 2 day) as `dd`,
  date_add(timestamp '1970-01-01 01:00:00.0', interval 2 hour) as `hh`,
  date_add(timestamp '1970-01-01 01:00:00.0', interval 2 minute) as `mi`,
  date_add(timestamp '1970-01-01 01:00:00.0', interval 2 second) as `ss`
from dual
 
-- POSTGRES:
select
  (timestamp '1970-01-01 01:00:00.0' + (2 || ' year')::interval) as "yy",
  (timestamp '1970-01-01 01:00:00.0' + (2 || ' month')::interval) as "mm",
  (timestamp '1970-01-01 01:00:00.0' + (2 || ' day')::interval) as "dd",
  (timestamp '1970-01-01 01:00:00.0' + (2 || ' hour')::interval) as "hh",
  (timestamp '1970-01-01 01:00:00.0' + (2 || ' minute')::interval) as "mi",
  (timestamp '1970-01-01 01:00:00.0' + (2 || ' second')::interval) as "ss"
 
-- SQLITE:
select
  datetime('1970-01-01 01:00:00.0', '+' || 2 || ' year') yy,
  datetime('1970-01-01 01:00:00.0', '+' || 2 || ' month') mm,
  datetime('1970-01-01 01:00:00.0', '+' || 2 || ' day') dd,
  datetime('1970-01-01 01:00:00.0', '+' || 2 || ' hour') hh,
  datetime('1970-01-01 01:00:00.0', '+' || 2 || ' minute') mi,
  datetime('1970-01-01 01:00:00.0', '+' || 2 || ' second') ss
 
-- DB2:
select
  (timestamp '1970-01-01 01:00:00.0' + 2 year) "yy",
  (timestamp '1970-01-01 01:00:00.0' + 2 month) "mm",
  (timestamp '1970-01-01 01:00:00.0' + 2 day) "dd",
  (timestamp '1970-01-01 01:00:00.0' + 2 hour) "hh",
  (timestamp '1970-01-01 01:00:00.0' + 2 minute) "mi",
  (timestamp '1970-01-01 01:00:00.0' + 2 second) "ss"
from "SYSIBM"."DUAL"
 
-- ORACLE:
select
  (timestamp '1970-01-01 01:00:00.0' + numtoyminterval(2, 'year')) "yy",
  (timestamp '1970-01-01 01:00:00.0' + numtoyminterval(2, 'month')) "mm",
  (timestamp '1970-01-01 01:00:00.0' + numtodsinterval(2, 'day')) "dd",
  (timestamp '1970-01-01 01:00:00.0' + numtodsinterval(2, 'hour')) "hh",
  (timestamp '1970-01-01 01:00:00.0' + numtodsinterval(2, 'minute')) "mi",
  (timestamp '1970-01-01 01:00:00.0' + numtodsinterval(2, 'second')) "ss"
from dual
 
-- SQLSERVER:
select
  dateadd(yy, 2, '1970-01-01 01:00:00.0') [yy],
  dateadd(mm, 2, '1970-01-01 01:00:00.0') [mm],
  dateadd(dd, 2, '1970-01-01 01:00:00.0') [dd],
  dateadd(hh, 2, '1970-01-01 01:00:00.0') [hh],
  dateadd(mi, 2, '1970-01-01 01:00:00.0') [mi],
  dateadd(ss, 2, '1970-01-01 01:00:00.0') [ss]
 
-- SYBASE:
select
  dateadd(yy, 2, '1970-01-01 01:00:00.0') [yy],
  dateadd(mm, 2, '1970-01-01 01:00:00.0') [mm],
  dateadd(dd, 2, '1970-01-01 01:00:00.0') [dd],
  dateadd(hh, 2, '1970-01-01 01:00:00.0') [hh],
  dateadd(mi, 2, '1970-01-01 01:00:00.0') [mi],
  dateadd(ss, 2, '1970-01-01 01:00:00.0') [ss]
from [SYS].[DUMMY]

Если вы до сих пор читали, вы поняли, что не следует выполнять арифметику даты и времени, используя дробные дни, как многие думают, когда должны писать Oracle SQL. Просто используйте встроенные функции и / или интервальные типы данных. Всегда!

39
Share: