Date formats in fields
In spreadsheets, it is common to see a wide variety of date formats. Sometimes they are based on one of the default ISO formats.
Elasticsearch uses a set of these built-in formats to understand date and time values. For more information, see the Elasticsearch reference documentation.
However, some spreadsheets use date formats that are not based on a default. To overcome this issue, you can specify the format to be used during import by using the Advanced Mapping option.
When a Date type is used, switch on Advanced Mapping to open a text field where you can specify a custom mapping in a JSON format.
Looking at the founded_date samples above, the custom mapping required is as follows:
{
"format": "dd/MM/yyyy HH:mm"
}
Ensure you use the correct case (upper or lower) for each symbol, as the meaning can vary with the case. |
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.
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 |