Formatting numbers and dates
Many transforms show a fields table with columns such as Format, Length, Precision, Decimal, Group and Currency. These columns are field metadata. They tell Hop how to read a string into a typed value, and how to write a typed value back to a string.
They look obvious after a decade of using them. They are not, especially Group, which is a thousands separator and not a row group.
This page is the single description of those columns. Transform and action pages that expose them link here instead of repeating the same one-liners.
When this metadata is used
Hop applies format metadata when it converts between a String and another type:
-
reading and writing text, CSV, XML, JSON, Excel and similar files
-
previewing rows in Hop Gui
-
parsing a constant Value (Row Generator, Add Constants, Data Grid)
-
concatenating fields, logging, and converting to String in scripts
Hop does not rewrite values when it writes to a relational database. The database driver receives the typed value (Integer, Number, Date, …). See Data types.
The shared field columns
The columns below appear on many field tables. They are field metadata: they control how Hop converts a string to a typed value, and how a typed value is written back to a string. They do not change the value stored in a database.
| Column | Description |
|---|---|
Format / Conversion mask | Number or date pattern used to parse and print the value. See Formatting numbers and dates. |
Length | String: maximum characters. Number / BigNumber: significant digits. Integer: display width (leading zeros when writing). Date: length of the printed string (for example |
Precision | Decimal digits after the radix for Number and BigNumber. Not used for String, Date or Boolean. |
Decimal | Character that separates the integer and fractional parts. |
Group / Grouping | Thousands grouping symbol. |
Currency | Optional currency symbol used when the format mask contains a currency sign ( |
Value | A single constant, entered as a string and parsed with Type, Format, Decimal, Group and Currency. Not a comma-separated list of values. Use Data Grid when you need several different rows. |
Set empty string? | When the constant is empty, emit an empty string ( |
Number formats
Number format masks follow Java DecimalFormat. See the Java 21 DecimalFormat documentation for the full pattern language.
| Symbol | Location | Localized | Meaning |
|---|---|---|---|
0 | Number | Yes | Digit |
# | Number | Yes | Digit, zero shows as absent |
. | Number | Yes | Decimal separator or monetary decimal separator |
- | Number | Yes | Minus sign |
, | Number | Yes | Grouping separator |
E | Number | Yes | Separates mantissa and exponent in scientific notation; need not be quoted in prefix or suffix |
; | Sub pattern boundary | Yes | Separates positive and negative sub patterns |
% | Prefix or suffix | Yes | Multiply by 100 and show as percentage |
\u2030 | Prefix or suffix | Yes | Multiply by 1000 and show as per mille |
¤ ( | Prefix or suffix | No | Currency sign, replaced by the currency symbol. If doubled, replaced by the international currency symbol. If present in a pattern, the monetary decimal separator is used instead of the decimal separator. |
' | Prefix or suffix | No | Used to quote special characters in a prefix or suffix, for example, |
In a pattern, the exponent character immediately followed by one or more digit characters indicates scientific notation. For example, 0.###E0 formats the number 1234 as 1.234E3.
Worked examples
The Decimal and Group columns change the characters that . and , in the mask stand for. They do not change the mask itself.
| Value | Format | Decimal | Group | Currency | Result |
|---|---|---|---|---|---|
1234.5 |
|
|
|
| |
1234.5 |
|
|
|
| |
1234.5 |
|
|
|
|
|
1234.5 |
|
|
|
| |
0.25 |
|
|
|
|
Leave Decimal and Group empty to use the JVM default locale. That is convenient on one machine and a source of surprises when the same pipeline runs in another country. Prefer an explicit Format, Decimal and Group whenever you parse or write numbers as text.
Date and timestamp formats
Date and timestamp format masks follow Java SimpleDateFormat. See the Java 21 SimpleDateFormat documentation for the full pattern language.
| 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 | 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) | 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 |
A Date value has millisecond precision. A Timestamp value can carry nanoseconds; the format mask still uses the SimpleDateFormat letters above, so only milliseconds (S) are expressed in the mask. See Data types for how Timestamp conversions work.
z, Z and X put a time zone or offset in the string. Without them, Hop reads and writes wall-clock time in the field’s Date Time Zone (or the JVM default). That is where daylight-saving gaps and overlaps show up. See String to Date and Date to String.
Common masks
| Mask | Example |
|---|---|
|
|
|
|
|
|
|
|
|
|
Quote literal letters with single quotes ('T' above). Unquoted letters are pattern symbols.
String to Date and Date to String
A Hop Date is a java.util.Date: an instant, stored as milliseconds since 1970-01-01 00:00:00 UTC. It does not remember a time zone. The time zone only matters when Hop converts that instant to a String, or a String back to an instant.
Hop uses Java SimpleDateFormat for both directions:
-
String → Date (
convertStringToDate) — the Format mask, Date Locale and Date Time Zone on the field tell the parser how to read the text. -
Date → String (
convertDateToString) — the same metadata prints the instant.
If you leave Date Time Zone empty, Hop uses the JVM default (TimeZone.getDefault()). A pipeline that parses cleanly on a laptop set to UTC can fail on a server set to America/New_York (EST/EDT) or Europe/Brussels (CET/CEST). Set the zone explicitly on the field (Select Values → Meta-data tab → Date Time Zone) whenever the string does not carry an offset.
Date Format Lenient? (same tab) is passed to SimpleDateFormat.setLenient. The default is not lenient: a value that is not a real wall-clock time in that zone throws
couldn’t convert string […] to a date using format […]
Lenient mode will invent a nearby valid time instead of failing. That hides bad data. Prefer a strict parse and an explicit zone.
Time zone letters in the mask
| Letter | What the string must contain | Example |
|---|---|---|
| RFC 822 offset, no colon |
|
| ISO 8601 offset ( |
|
| Named zone or GMT offset |
|
If the mask has no Z, X or z, the digits in the string are a wall-clock time in the field’s Date Time Zone (or the JVM default). They are not "just UTC" unless that zone is UTC.
Good round-trip masks:
-
yyyy-MM-dd’T’HH:mm:ss.SSSX→2026-03-08T07:30:00.000Z -
yyyy-MM-dd HH:mm:ssZ→2026-03-08 07:30:00+0000
Daylight saving time: the missing hour and the doubled hour
Regions that observe daylight saving time (DST) change the civil clock twice a year. The UTC timeline never skips or repeats. The local wall clock does.
Spring forward (gap). Clocks jump ahead one hour. That local hour does not exist.
-
America/New_York(EST → EDT): second Sunday of March,02:00becomes03:00. In 2026 that is2026-03-08.2026-03-08 02:30:00is not a real local time. -
Europe/Brussels(CET → CEST): last Sunday of March,02:00becomes03:00. In 2026 that is2026-03-29.2026-03-29 02:30:00is not a real local time.
Fall back (overlap). Clocks jump back one hour. That local hour occurs twice.
-
America/New_York(EDT → EST): first Sunday of November,02:00becomes01:00. In 2026 that is2026-11-01.2026-11-01 01:30:00happens first as EDT (UTC−4) and again as EST (UTC−5). -
Europe/Brussels(CEST → CET): last Sunday of October,03:00becomes02:00. In 2026 that is2026-10-25.2026-10-25 02:30:00happens twice.
| Direction | Gap (spring) | Overlap (fall) |
|---|---|---|
String → Date | A timezone-less string whose local time falls in the missing hour throws (strict parse). Typical Hop error: | The same local time maps to two instants. |
Date → String | Every instant still prints. A UTC instant just after the jump prints as | Two different instants print as the same local string if the mask has no |
The UTC-string-in-a-local-JVM trap
This is the one that bites.
Source systems often store UTC as a naive string: 2026-03-29 02:30:00, meaning 02:30 UTC, with no Z and no offset. That instant is perfectly valid in UTC (it is 03:30 CEST, or 21:30 the previous evening in EDT).
If Hop parses that string with Format yyyy-MM-dd HH:mm:ss and the JVM (or the field) is in Europe/Brussels or America/New_York, the parser treats 02:30 as local wall-clock time. On the spring-forward night that local time does not exist, and the convert throws — even though the original value was a normal UTC timestamp.
The same string in November can parse, but to the wrong instant of the two 01:30 / 02:30 locals.
Fixes, in order of preference:
-
Put the offset in the string and in the mask (
ZorX). Then the JVM default zone no longer matters. -
Keep the naive UTC string, but set Date Time Zone to
UTCon the field that parses it (and on the field that writes it back). -
Do not rely on Date Format Lenient = Y to paper over the gap. Lenient parsing will shift
02:30to some other local time and you will load the wrong instant with no error.
When you write dates to text, CSV, JSON or logs from a DST-observing JVM, use a mask with an offset, or set the output field’s Date Time Zone to UTC. Otherwise a reader in another zone — or the same pipeline after a DST weekend — will not reconstruct the instant you had.
Rounding
Rounding Types
Rounding on Number and BigNumber data type fields is based on Java Rounding Mode
By default, rounding mode Half Even is used this Rounding mode will round towards the "nearest neighbor" unless both neighbors are equidistant, in which case, round towards the even neighbor.
Example: Rounding from 1 to 0 digits
5.5 → 6
2.5 → 2
-2.5 → -2
-5.5 → -6
Unnecessary
Rounding mode to assert that the requested operation has an exact result, hence no rounding is necessary. This mode will throw an error when you try to reduce the precision of a number
Half Down
Rounding mode to round towards "nearest neighbor" unless both neighbors are equidistant, in which case round down.
Half Even
Rounding mode to round towards the "nearest neighbor" unless both neighbors are equidistant, in which case, round towards the even neighbor.
Half Up
Rounding mode to round towards "nearest neighbor" unless both neighbors are equidistant, in which case round up.
Examples
| Input Number | Up | Down | Ceiling | Floor | Half Up | Half Down | Half Even | Unnecessary |
|---|---|---|---|---|---|---|---|---|
5.5 | 6 | 5 | 6 | 5 | 6 | 5 | 6 | throw ArithmeticException |
2.5 | 3 | 2 | 3 | 2 | 3 | 2 | 2 | throw ArithmeticException |
1.6 | 2 | 1 | 2 | 1 | 2 | 2 | 2 | throw ArithmeticException |
1.1 | 2 | 1 | 2 | 1 | 1 | 1 | 1 | throw ArithmeticException |
1.0 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
-1.0 | -1 | -1 | -1 | -1 | -1 | -1 | -1 | -1 |
-1.1 | -2 | -1 | -1 | -2 | -1 | -1 | -1 | throw ArithmeticException |
-1.6 | -2 | -1 | -1 | -2 | -2 | -2 | -2 | throw ArithmeticException |
-2.5 | -3 | -2 | -3 | -3 | -3 | -2 | -2 | throw ArithmeticException |
-5.5 | -6 | -5 | -6 | -6 | -6 | -5 | -6 | throw ArithmeticException |
Common mistakes
Treating Group as a category. Group is the thousands separator (, or .). It does not group rows. Use Group By or Memory Group By for that.
Putting several values in one Value cell. Value is a single constant. 1,2,3 is one string, not three rows. Use Data Grid when you need more than one distinct row.
Swapping Decimal and Group. 1.234,50 needs Decimal , and Group .. The opposite pair will not parse, or will parse the wrong number.
Leaving Format empty for locale-specific numbers. Without an explicit mask, Hop uses JVM defaults. A pipeline that works on a US laptop can fail on a European server.
Expecting Length to change a Date’s internal value. Length 4 on a Date only affects how that date is printed as a string (typically the year). The Date on the stream is still a full date-time.
Parsing a UTC date string in a local time zone. 2026-03-29 02:30:00 is a normal UTC timestamp and a missing local hour in CEST. Set Date Time Zone to UTC, or include Z / X in the mask. See String to Date and Date to String.
Sending large integers through JavaScript. JavaScript numbers are IEEE 754 doubles. Integers longer than about 15–16 digits can change. See JavaScript — Numeric values.