Table of Content

• Introduction
• Logical reasoning
• Typical project phases
• Local time
• Sending to client
• Manifesto :)
• Facts
• Rules
• Investigations
• Introduction
• Create and populate table
• Populate other columns with conversion
• Find storage requirements for above data
• Results
• Links

Introduction

Logical reasoning

Typical project phases

Phase 1 - project start. Database created fast and dirty, to get immediate results and applause. Any time is TIMESTAMP, current time is CURRENT_TIMESTAMP, any day is again TIMESTAMP with 0:00:00.000000 time. In best scenario day is DATE with missing time.

Phase 2 - project grows, dirty and temporary data became fixed and production ready (of course, too late to change something).

Phase 3 - real data and real millions of records come, and everything becomes SLOW and out-of-memory.

Local time

Sending to client

Manifesto :)

Facts

• FACT - Databases do not provide simple and efficient date and time handling
• FACT - Transaction protocols do not provide simple and efficient date and time handling
• FACT - Most of projects suffer from database bottleneck

Rules

• RULE - Database, server side processing and network transactions - process date/time only in UTC
• RULE - moments of time are presented as milliseconds from epoch in UTC - 8 bytes/64 bits numeric - long in Java - one word on x64 platform;
• RULE - days are presented as days from epoch in UTC - 2 bytes - short in Java;
• RULE - time (milliseconds) is stored as NUMBER(13) in Oracle, or BIGINT in other databases;
• RULE - a day is stored as NUMBER(5) in Oracle, or SMALLINT in other databases;
• RULE - milliseconds and days are sent over network as above explained numbers - longs and shorts - easy serialized in the most portable way;
• RULE - milliseconds and days are converted to a user readable strings only on client side (normally in JS) - when local zone, i18n, l10n are known.

Investigations

Introduction

Create and populate table

create table TIMING_SIZE (
  MS13 NUMBER(13),  -- milliseconds from epoch - exactly 13 digits
  MS NUMBER,        -- milliseconds from epoch - lazy version
  DAY5 NUMBER(5),   -- day from epoch - exactly 5 digits
  DAYL NUMBER,      -- day from epoch - lazy version
  TS TIMESTAMP,     -- this is normally how it is designed - nanoseconds which are never used
  TS3 TIMESTAMP(3), -- should be stored with millisecond precision
  DAYX DATE         -- this is normally how it is designed
);
insert into TIMING_SIZE (TS, TS3, DAYX) values (CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, CURRENT_DATE);

Populate other columns with conversion

update TIMING_SIZE set DAY5 = extract(day from (TS - TIMESTAMP '1970-01-01 00:00:00.000'));

update TIMING_SIZE set DAYL = DAY5,
  MS13 = DAY5 * 24 * 3600000 +
          extract(hour from (TS - TIMESTAMP '1970-01-01 00:00:00.000')) * 3600000 +
          extract(minute from (TS - TIMESTAMP '1970-01-01 00:00:00.000')) * 60000 +
          extract(second from (TS - TIMESTAMP '1970-01-01 00:00:00.000')) * 1000;

update TIMING_SIZE set MS = MS13;
select * from TIMING_SIZE;

MS13          MS            DAY5  DAYL  TS                              TS3                             DAYX                        
1519145687690 1519145687690 17582 17582 20-FEB-18 04.54.47.690000000 PM 20-FEB-18 04.54.47.690000000 PM 20-FEB-18                   

Find storage requirements for above data

select vsize(MS13), vsize(MS), vsize(DAY5), vsize(DAYL), vsize(TS), vsize(TS3), vsize(DAYX)
from TIMING_SIZE;

VSIZE(MS13) VSIZE(MS) VSIZE(DAY5) VSIZE(DAYL) VSIZE(TS) VSIZE(TS3) VSIZE(DAYX)                 
8           8         4           4           11        11         7                           

Results

• In all cases simple numeric representation of time information take less time than standard Oracle types;
• Setting size of numeric columns does not affect storage requirements;
• For timestamps best to use MS NUMBER, which takes 8 bytes;
• For days best to use DAYL NUMBER, which takes 4 bytes.
• For

Taking into account other systems

Links