Mapping BCE Dates: Two Approaches

BCE dates cause subtle "off-by-one" bugs between Java and databases. dbVisitor 6.7.0 adds JulianDayTypeHandler and PgDateTypeHandler to provide cross-database and PostgreSQL-native mappings. Database date ranges and handler boundaries still need consideration.

Regression examples pinned to 6.8.0: GitHub / Gitee.

Year Representation

Java's LocalDate uses ISO 8601, where Year 0 represents 1 BC:

Java Year	Meaning	PostgreSQL Representation
1	1 AD	0001-01-01
0	1 BC	0001-01-01 BC
-1	2 BC	0002-01-01 BC
-99	100 BC	0100-01-01 BC

Conversion formula: BC year = |Java Year| + 1

LocalDate uses the proleptic Gregorian calendar, while traditional java.sql.Date conversions involve legacy calendar handling; they are not equivalent for every historical date. Different JDBC drivers also handle BCE dates inconsistently — some even throw exceptions outright.

Julian Day Mapping

The Julian Day Number (JDN) is a continuous date counting system from astronomy, counting continuously from a fixed epoch. Calendar and day-boundary conventions still matter; this handler maps ISO LocalDate to integer day numbers.

Principle: Convert a LocalDate to a BIGINT integer for storage, then reverse the conversion on read.

// Store: 100 BC → Julian Day Number 1684901
LocalDate bcDate = LocalDate.of(-99, 1, 1);

Map<String, Object> params = new HashMap<>();
params.put("id", 1);
params.put("date", bcDate);

jdbcTemplate.executeUpdate(
    "INSERT INTO events (id, julian_day) VALUES (#{id}, #{date, typeHandler=net.hasor.dbvisitor.types.handler.time.JulianDayTypeHandler})",
    params
);

// Read: Julian Day Number 1684901 → 100 BC
LocalDate loaded = jdbcTemplate.queryForObject(
    "SELECT julian_day FROM events WHERE id = ?",
    new Object[] { 1 },
    (rs, rowNum) -> new JulianDayTypeHandler().getResult(rs, "julian_day")
);

// Expected: loaded.equals(bcDate); Year -99 represents 100 BC.

Core Algorithm (Richards 2012):

// LocalDate → Julian Day Number
int a = (14 - month) / 12;
int y2 = year + 4800 - a;
int m2 = month + 12 * a - 3;
long jdn = day + (153 * m2 + 2) / 5 + 365 * y2 + y2 / 4 - y2 / 100 + y2 / 400 - 32045;

Best suited for:

• Projects requiring cross-database consistency (MySQL, PostgreSQL, Oracle, SQLite, etc.)
• Historical and astronomical data
• Requires BIGINT storage, not native DATE types
• Integer arithmetic does not guarantee the entire LocalDate range; constrain the business range and verify round trips.

PostgreSQL DATE Mapping

If your project exclusively targets PostgreSQL, you can leverage its native BC date format and store directly as a DATE type.

LocalDate bcDate = LocalDate.of(-99, 1, 1);

Map<String, Object> params = new HashMap<>();
params.put("id", 1);
params.put("date", bcDate);

jdbcTemplate.executeUpdate(
    "INSERT INTO events (id, event_date) VALUES (#{id}, #{date, typeHandler=net.hasor.dbvisitor.types.handler.time.PgDateTypeHandler})",
    params
);

// Stored in database as: 0100-01-01 BC
LocalDate loaded = jdbcTemplate.queryForObject(
    "SELECT event_date FROM events WHERE id = ?",
    new Object[] { 1 },
    (rs, rowNum) -> new PgDateTypeHandler().getResult(rs, "event_date")
);
// loaded.equals(bcDate)

The parameter's typeHandler applies only to writing. Reading must also use PgDateTypeHandler, either through the RowMapper above or an entity field mapping. Create events(id INTEGER PRIMARY KEY, event_date DATE) for this example; the first approach instead needs julian_day BIGINT.

Advantages:

• Uses the native DATE type, allowing direct SQL querying and comparison (e.g., WHERE event_date < '0500-01-01 BC')
• No additional type conversion layer required

Caveats:

• Do not assume lossless mapping for every historical date, especially BC leap days and native DATE boundaries. Verify driver and handler round trips for required dates.
• PostgreSQL only

Comparison

Dimension	JulianDayTypeHandler	PgDateTypeHandler
Database support	All (stored as BIGINT)	PostgreSQL only
Storage type	BIGINT	DATE
SQL date comparison	Numeric comparison (works but less intuitive)	Native date comparison
Precision	Day-level (no time)	Day-level (no time)
Date boundaries	Verify integer conversion range	Verify BC leap days and native DATE range
Migration cost	Low (generic integer column)	Medium (PG-dependent)

Recommendation: Use JulianDayTypeHandler for cross-database projects or scenarios requiring strict consistency; use PgDateTypeHandler for PostgreSQL-exclusive projects that need native SQL date operations.