Date formats in fields

If you are creating or editing a record and the date or time format you enter is not accepted, contact your system administrator. The format might have been customized during the data import process.

In spreadsheets, it is common to see a wide variety of date and time formats. Sometimes, they are based on one of the default ISO formats, such as yyyy-MM-ddTHH:mm:ss.

Siren Investigate supports the Elasticsearch built-in formats to parse date and time values. For more information, see the Elasticsearch documentation.

However, some spreadsheets use date formats that are not based on a default.

Defining an advanced mapping

If you are creating a new entity table and importing data, you can overcome this issue by specifying the date format you want to use.

If you are importing data into an existing entity table, you can transform the content of a date field by applying a field script. For more information, see Field script examples.

  1. On the Define structure screen, browse for the fields that contain a date or time.

  2. Select Date from the Type dropdown menu and select Define advanced mapping.

  3. Enter a custom mapping expression in JSON syntax. For example:

    {
  "format": "dd/MM/yyyy HH:mm"
}

    Modifying the date format as an advanced mapping

  4. Set the same custom mapping expression for all other fields that contain dates.

This affects the date format that is accepted when records are created or edited in the record view panel. If users are not aware of the custom date format, it is important to advise them of this customization to help them avoid errors.

Formatting conventions for date and time

The following table contains information to help you construct most date- and time-based custom mappings.

For more detailed information, see DateTimeFormatter in the Java documentation.

Ensure you use the correct case (upper or lower) for each symbol, as the meaning can vary with the case.

All letters 'A' to 'Z' and 'a' to 'z' are reserved as pattern letters. The following pattern letters are defined:

Symbol Meaning Presentation Examples

G

era

text

AD; Anno Domini; A

u

year

year

2004; 04

y

year-of-era

year

2004; 04

D

day-of-year

day

189

M/L

month-of-year

number/text

7; 07; Jul; July; J

d

day-of-month

number

10

Q/q

quarter-of-year

number/text

3; 03; Q3; 3rd quarter

Y

week-based-year

year

1996; 96

w

week-of-week-based-year

number

27

W

week-of-month

number

4

E

day-of-week

test

Tue; Tuesday; T

e/c

localized day-of-week

number/text

2; 02; Tue; Tuesday; T

F

week-of-month

number

3

a

am-pm-of-day

text

PM

h

clock-hour-of-am-pm (1-12)

number

12

k

hour-of-am-pm (0-11)

number

0

K

clock-hour-of-am-pm (1-24)

number

0

H

hour-of-day (0-23)

number

0

m

minute-of-hour

number

30

s

second-of-minute

number

55

S

fraction-of-second

fraction

978

A

milli-of-day

number

1234

n

nano-of-second

number

987654321

N

nano-of-day

number

1234000000

V

time-zone ID

zone-id

America/Los_Angeles; Z; -08:30

z

time-zone name

zone-name

Pacific Standard Time; PST

O

localized zone-offset

offset-O

GMT+8; GMT+08:00; UTC-08:00;

X

zone-offset 'Z' for zero

offset-X

Z; -08; -0830; -08:30; -083015; -08:30:15;

x

zone-offset

offset-x

+0000; -08; -0830; -08:30; -083015; -08:30:15;

Z

zone-offset

offset-Z

+0000; -0800; -08:00;

p

pad next

pad modifier

1

'

escape for text

delimiter

''

single quote

literal

'

[

optional section start

]

optional section end

#

Reserved for future use

\{

Reserved for future use

}

Reserved for future use