We are building India Accounting OS, a GST and TDS-ready accounting engine on Node.js and PostgreSQL. One of its controls is period locking: once a month is closed, nothing can be posted into it. The very first time the integration tests ran against a real database, the test 'posting into a locked period is refused' failed. The posting went through. The lock existed, but it was being checked against the wrong month.
What actually happened
The node-postgres driver (pg) converts a PostgreSQL DATE column into a JavaScript Date object at midnight in the server's local time. On a machine set to India time (UTC+5:30), the start of the financial year, 1 April 2026, arrives as 31 March 2026, 18:30 UTC. Our code then read the month with getUTCMonth() and got March. Every period index shifted by one, so the check for 'is April locked?' was really asking about March.
Why tests and reviews missed it
Unit tests that build dates in JavaScript never go through the database driver, so they passed. And on a server running in UTC — which most cloud servers do — the conversion lands exactly on midnight and the bug disappears. It only shows up with a real database round-trip on a machine with a positive UTC offset. In other words, it would have appeared for Indian users and never on a typical CI server.
Why it mattered so much
A control that silently doesn't apply is worse than having no control. A user who sees a lock icon trusts the books for that month. If an entry can slip into a closed GST period, returns already filed no longer match the books, and nobody finds out until reconciliation.
The fix
We told the driver to leave DATE values alone: register a type parser for PostgreSQL's DATE type (OID 1082) that returns the raw 'YYYY-MM-DD' string. Month and year are now read by splitting that string, with no Date object anywhere in the path. We did the same for money: NUMERIC (OID 1700) comes back as a string and goes into a fixed-point decimal type, so rupee amounts never touch a floating-point number. Tests now pin the month boundaries, and the suite runs on servers in both IST and UTC.
The rule we follow now
An accounting date is a calendar label, not a moment in time. An invoice dated 1 April is dated 1 April in every timezone. Store it as DATE, carry it as a string or a dedicated date type, and only turn it into a timestamp when you genuinely mean a moment — like when a user logged in. If your app handles invoices, salaries, GST periods or due dates in JavaScript, search your code for new Date( applied to a database date. That's where this bug lives.
What else the database enforces
The same engine puts its key rules in PostgreSQL itself rather than only in application code: debit must equal credit (checked when the transaction commits), posted entries can be reversed but never deleted, and tax rules carry effective-from and effective-to dates so a rule change can't rewrite past entries. Read more in our India Accounting OS case study.
Frequently asked questions
Why is my PostgreSQL date one day off in Node.js?
node-postgres converts a DATE into a JavaScript Date at local midnight. In India (UTC+5:30) that is 18:30 UTC on the previous day, so reading UTC parts gives the wrong day or month.
How do I stop node-postgres converting DATE to a JavaScript Date?
Register a type parser for OID 1082 that returns the raw value, e.g. types.setTypeParser(1082, v => v), and work with the 'YYYY-MM-DD' string.
How should money be handled in Node.js with PostgreSQL?
Store it as NUMERIC, have the driver return it as a string (OID 1700), and calculate with a fixed-point decimal type so amounts never pass through floating-point numbers.
Why didn't my tests catch the timezone bug?
Unit tests often skip the database round-trip, and CI servers usually run in UTC, where the conversion lands exactly on midnight. Run integration tests against a real database in both UTC and your users' timezone.
Amit has 6+ years of experience building data pipelines, AI agents, and automation systems for businesses across India and globally. He founded HowAutomate to make enterprise-grade automation accessible to growing businesses.