v6.7.0: BCE Date Handling

Hint

This article is generated by AI translation.

BCE dates cause subtle "off-by-one" bugs between Java and databases. dbVisitor 6.7.0 adds JulianDayTypeHandler and PgDateTypeHandler to solve this from two angles: cross-database portability and PostgreSQL-native support.

Root Cause: Ambiguity in 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

Meanwhile, java.sql.Date uses the Proleptic Gregorian Calendar internally, which introduces offset issues during date conversion. Different JDBC drivers also handle BCE dates inconsistently — some even throw exceptions outright.

Approach 1: JulianDayTypeHandler — Cross-Database Solution

The Julian Day Number (JDN) is a continuous date counting system from astronomy, starting from January 1, 4713 BC, with no calendar-system ambiguity.

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

// Store: 100 BC → Julian Day Number 1684534
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 1684534 → 100 BC
LocalDate loaded = jdbcTemplate.queryForObject(
    "SELECT julian_day FROM events WHERE id = ?",
    new Object[] { 1 },
    (rs, rowNum) -> new JulianDayTypeHandler().getResult(rs, "julian_day")
);

assertEquals(bcDate, loaded);  // ✔ Pass
assertEquals(-99, loaded.getYear());  // ✔ Year -99 = 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
• Only requires a BIGINT column — no dependency on native DATE types

Approach 2: PgDateTypeHandler — PostgreSQL-Native Solution

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
// Automatically converted back to LocalDate.of(-99, 1, 1) on read

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:

• ISO 8601 leap year rules differ from PostgreSQL BC leap year rules. In Java, Year -4 is a leap year (ISO leap year), but the converted 5 BC is not a leap year in PostgreSQL.
• 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)
Leap year compatibility	No ambiguity (pure numeric)	Mind the BC leap year difference
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.