Date and Time Formatting in PilotFish Data Mapper
Introduction
The PilotFish Date and Time Formatter in our Data Mapper is a powerful tool for reformatting date and time strings. By defining the structure of both the input and output dates, PilotFish is able to instantly transform the format without losing any data.
If unfamiliar with date and time patterns, defining one may seem intimidating at first, however, they are really quite simple. A date and time pattern is a series of characters that refer to different components of the date & time. For example, M is for the month, y for the year, h for the hour, etc.
NOTE: It is important to remember that date and time patterns are case-sensitive. For example, M is month, while m is minute.
A common date and time format example is: 2018-01-01 13:01:01.123. That string has all the main components of a date/time format: year, month, day, hour, minute, second, millisecond. As a pattern, it would be: yyyy-MM-dd HH:mm:ss.SSS. It’s important to note the capital H, which is used for hours in the 24-hour format. AM and PM use the lower-case h.
All Pattern Characters
The following is a list of all pattern characters. PilotFish supports all the patterns from Java’s SimpleDateFormat API.
| Letter | Date or Time Component | Presentation | Examples |
|---|---|---|---|
G | Era designator | Text | AD |
y | Year | Year | 1996; 96 |
Y | Week year | Year | 2009; 09 |
M | Month in year (context sensitive) | Month | July; Jul; 07 |
L | Month in year (standalone form) | Month | July; Jul; 07 |
w | Week in year | Number | 27 |
W | Week in month | Number | 2 |
D | Day in year | Number | 189 |
d | Day in month | Number | 10 |
F | Day of week in month | Number | 2 |
E | Day name in week | Text | Tuesday; Tue |
u | Day number of week (1 = Monday, …, 7 = Sunday) | Number | 1 |
a | Am/pm marker | Text | PM |
H | Hour in day (0-23) | Number | 0 |
k | Hour in day (1-24) | Number | 24 |
K | Hour in am/pm (0-11) | Number | 0 |
h | Hour in am/pm (1-12) | Number | 12 |
m | Minute in hour | Number | 30 |
s | Second in minute | Number | 55 |
S | Millisecond | Number | 978 |
z | Time zone | General time zone | Pacific Standard Time; PST; GMT-08:00 |
Z | Time zone | RFC 822 time zone | -0800 |
X | Time zone | ISO 8601 time zone | -08; -0800; -08:00 |
More Information
For more information, please check the Java documentation on date formatting at this link: Simple Date Format