Easily misused data types: why a 23:30 order lands on the next day and totals are off by 1 VND Basic

The problem

A shop selling in Vietnam and Japan shares one database. Order 1, worth 450,000 VND, is created at 23:30 on Oct 22, Vietnam time. A revenue report rerun by the team in Tokyo puts this order on Oct 23.

The same week, tax reconciliation is off by 1 VND: ceil(450000 * 1.1) computed in float8 gives 495,001 instead of 495,000.

The article Numbers, dates, strings lists the mistakes on the Node side. This one goes into PostgreSQL 17’s mechanics: which value is actually stored, and what decides how it is displayed?

What to choose (summary first)

Situation	Choose	Condition
Event time: order created, payment	timestamptz	Daily reports state the time zone explicitly with AT TIME ZONE
“Wall clock” time: opens at 08:00	timestamp or time	No comparison of instants across countries
VND, JPY money (no minor unit)	bigint	Tax and FX computed in numeric, then rounded deliberately
Money with fractions, exchange rates	numeric(p, s)	Accept slower math than integers
Measurements, coordinates	float8	No = comparisons, never for money
Strings	text (+ CHECK if needed)	Avoid char(n)

Core concepts

Type	Stores	Displayed as
timestamp	A “bare” date and time, no time zone	Exactly as stored
timestamptz	An absolute instant, internally UTC	Per the session’s TimeZone
numeric(p, s)	Exact decimal (p digits, s after the point)	Verbatim
float8 / real	8 / 4 byte binary floating point	Shortest exact-enough form
text, varchar(n), char(n)	varlena string	char(n) pads with spaces to n

• timestamptz does not store a time zone: the input zone is only used to convert to UTC, then discarded.
• timestamp silently drops the input offset: TIMESTAMP '2026-10-22 23:30:00+07' gives 2026-10-22 23:30:00.

How it works

timestamptz: stored as UTC, displayed per session

The question: order 1 is stored once, so why do two readers see two different days?

Order 1 stores one UTC value; a Tokyo session reads day 23, AT TIME ZONE brings it back to day 22

Enlarge diagram

App (Node) → PostgreSQL23:30+07:00

◀ Prev▶ PlayNext ▶↺ RestartStep 1/5

The app sends order 1's Date with an offset: 2026-10-22T23:30+07:00.

Step	Session sees (the stored value is always 16:30 UTC)
3. TimeZone=UTC	2026-10-22 16:30:00+00
4. TimeZone=Asia/Tokyo	2026-10-23 01:30:00+09, ::date = 23
5. AT TIME ZONE 'Asia/Ho_Chi_Minh'	2026-10-22 23:30:00 in every session

created_at::date cuts the day by the TimeZone of the session running the report, not by where the order was created.

AT TIME ZONE flips direction with the input type

Loading diagram…

• timestamptz AT TIME ZONE z → timestamp: “what did the clock in z show at that instant?”.
• timestamp AT TIME ZONE z → timestamptz: “this is local time in z, convert it to an absolute instant”.

Real output (session TimeZone=UTC):

SELECT TIMESTAMP   '2026-10-22 23:30'    AT TIME ZONE 'Asia/Ho_Chi_Minh' AS a,
       TIMESTAMPTZ '2026-10-22 23:30+07' AT TIME ZONE 'Asia/Ho_Chi_Minh' AS b;
--            a            |          b
-- ------------------------+---------------------
--  2026-10-22 16:30:00+00 | 2026-10-22 23:30:00

Revenue per Vietnam day, run from a Tokyo session:

SET TimeZone = 'Asia/Tokyo';
SELECT created_at::date AS day, sum(total_vnd) FROM orders GROUP BY 1;
--     day     |  sum
--  2026-10-23 | 570000     -- order 1 (Oct 22 VN time) is lumped into Oct 23

SELECT date_trunc('day', created_at AT TIME ZONE 'Asia/Ho_Chi_Minh') AS vn_day,
       sum(total_vnd) FROM orders GROUP BY 1 ORDER BY 1;
--        vn_day        |  sum
--  2026-10-22 00:00:00 | 450000
--  2026-10-23 00:00:00 | 120000

To filter one VN day, compare a range on the column itself so a B-tree index can be used: created_at >= TIMESTAMP '2026-10-22 00:00' AT TIME ZONE 'Asia/Ho_Chi_Minh' and < the same bound for day 23.

numeric and float8

float8 stores numbers in base 2, so 0.1 is only an approximation. The docs classify float as inexact, recommend numeric for money, and describe numeric arithmetic as “very slow” compared with integers and floats.

SELECT 0.1::float8 + 0.2::float8 AS f8, 0.1::numeric + 0.2 AS num,
       ceil(450000 * 1.1::float8) AS f8_ceil, ceil(450000 * 1.1::numeric) AS num_ceil;
--          f8          | num | f8_ceil | num_ceil
--  0.30000000000000004 | 0.3 |  495001 |   495000

SELECT round(2.5::float8) AS f8_r, round(2.5::numeric) AS num_r;
--  f8_r | num_r
--     2 |     3

• 450000 * 1.1 in float gives 495000.00000000006; ceil pushes it up by 1 VND.
• Different rounding rules: numeric rounds half away from zero, while float8 (on most machines) rounds half to even.
• real is only precise to about 6 digits.

text, varchar(n), char(n)

The docs say there is no performance difference among the three, apart from the extra storage of char(n) and a few CPU cycles for length checks; char(n) is usually the slowest.

CREATE TABLE t (c char(5), v varchar(5), x text CHECK (char_length(x) <= 5));
INSERT INTO t VALUES ('ab', 'ab', 'ab');
SELECT '[' || c || ']' AS c_concat, octet_length(c), length(c), c = 'ab   ' AS eq FROM t;
--  c_concat | octet_length | length | eq
--  [ab]     |            5 |      2 | t
INSERT INTO t (v) VALUES ('abcdef');  -- ERROR: value too long for type character varying(5)
INSERT INTO t (x) VALUES ('abcdef');  -- ERROR: ... violates check constraint "t_x_check"
SELECT 'abcdef'::varchar(5);          -- abcde (explicit cast truncates silently)

char(n) stores the padding spaces (5 bytes) but ignores trailing spaces when comparing and when converting to text. A CHECK limits length like varchar(n); changing the limit only means replacing the constraint.

Operational details

• now() = transaction_timestamp(): the transaction start time. Demo: after pg_sleep(1.2) in the same transaction, now() stays at 13:15:13.171729 while clock_timestamp() has moved to 13:15:14.379239. A long transaction writing now() records a time earlier than the commit.
• TimeZone can be set per server, role, database or session. The demo container defaults to UTC.
• Strings max out around 1 GB; ≤ 126 bytes costs a 1-byte header, long strings are compressed or moved to TOAST (see storage layout).

Real-world example

VN + Japan shop: a Tokyo pod writes into a timestamp column

Column created_local timestamp receives a Date from the app. node-postgres sends a Date in the process’s local time. A pod with TZ=Asia/Ho_Chi_Minh stores 2026-10-22 23:30:00; a pod with TZ=Asia/Tokyo stores 2026-10-23 01:30:00 for the same instant.

The order is already on the wrong day in the data itself, and AT TIME ZONE cannot fix it.

Demo examples/src/data-type-pitfalls/demo.ts (node-postgres 8.23, PostgreSQL 17.11). Row 1 is written by SQL as 23:30; row 2 is sent by Node as new Date('2026-10-22T16:30:00Z'):

TZ=Asia/Ho_Chi_Minh npx --prefix examples ts-node examples/src/data-type-pitfalls/demo.ts
TZ=Asia/Tokyo       npx --prefix examples ts-node examples/src/data-type-pitfalls/demo.ts

process TZ=Asia/Ho_Chi_Minh, session TimeZone=UTC
timestamp   -> 2026-10-22T16:30:00.000Z
timestamptz -> 2026-10-22T16:30:00.000Z
numeric     -> string "450000"
bigint      -> string "450000"
count(*)    -> string "1"
Date -> timestamp stored   : 2026-10-22 23:30:00
Date -> timestamptz stored : 2026-10-22 16:30:00+00

process TZ=Asia/Tokyo, session TimeZone=UTC
timestamp   -> 2026-10-22T14:30:00.000Z
timestamptz -> 2026-10-22T16:30:00.000Z
...
Date -> timestamp stored   : 2026-10-23 01:30:00

1. timestamptz → Date is the correct instant under every TZ; timestamp is interpreted in the process’s TZ.
2. numeric, bigint and count(*) come back as strings by default.
3. An easy-to-miss exception: numeric[] becomes an array of floats, so 12345678901234567.89 comes out as 12345678901234568.

Trade-offs

Pros

• timestamptz gives a single instant that compares correctly across every pod and session.

• numeric gives exact results for addition, subtraction and multiplication, with clear rounding rules.

• bigint in the smallest unit: fast, compact 8 bytes, exact sums.

< slot="cons">

• timestamptz does not remember the original zone; if you need the customer’s local time, store a time zone name column too.

• numeric is slower than integers and floats; the driver returns strings, so the app needs a decimal library.

• bigint forces every division (tax, FX) to commit to a rounding rule.

Cons

Production checklist

1. Every “when did the event happen” column is timestamptz; audit existing timestamp columns.
2. Daily reports state the business time zone explicitly with AT TIME ZONE or date_trunc(..., 'Asia/Ho_Chi_Minh'), never relying on the session’s TimeZone.
3. Set TZ=UTC for every app process so behavior matches across environments.
4. Money: bigint in the smallest unit (VND, JPY) or numeric(p, s), with a currency column; no money columns of type real/float8.
5. Decide how the driver parses int8, numeric and numeric[], and apply it app-wide.
6. Use text + CHECK (char_length(...) <= n) or varchar(n); do not create new char(n) columns.

Common mistakes

The most common mistake is converting twice:

Applying AT TIME ZONE twice or to the wrong type

✕ Common

SET TimeZone = 'Asia/Tokyo';
SELECT created_at::timestamp AT TIME ZONE 'Asia/Ho_Chi_Minh' FROM orders;
-- 2026-10-23 03:30:00+09  (correct is 01:30+09): ::timestamp takes Tokyo time and treats it as VN time

✓ Prefer

SELECT created_at AT TIME ZONE 'Asia/Ho_Chi_Minh' AS vn_time FROM orders;
-- 2026-10-22 23:30:00 in every session

Why

Check the type with pg_typeof(...) before adding AT TIME ZONE.

The second mistake comes from how length limits are written:

Believing a cast to varchar(n) raises an error when too long

INSERTing an over-long string into varchar(5) raises an error, but 'abcdef'::varchar(5) silently truncates to abcde. Migrations or ETL with explicit casts lose data without any error.

Misconception	Consequence	What to do
timestamptz stores the original zone	No way to tell whether the order came from VN or Japan	Store a time zone column if needed
created_at::date is the day where the sale happened	The report changes with the session	AT TIME ZONE the business zone
real is enough for prices	Wrong from the 7th digit	bigint or numeric

Self-check

1. Two sessions with different TimeZone read the same timestamptz row. Is the stored value different? Why does ::date differ?
2. What type does x AT TIME ZONE 'Asia/Tokyo' return when x is timestamp, and when x is timestamptz?
3. Why does ceil(450000 * 1.1::float8) give 495,001 while numeric gives 495,000?

Summary

Key takeaways

• timestamptz stores an instant (UTC internally) and displays it per the session’s TimeZone; timestamp has no time zone and ignores the input offset.
• AT TIME ZONE flips direction with the input type; daily reports must state the business time zone.
• Money: bigint in the smallest unit or numeric; float8 has binary error and different rounding rules.
• text and varchar(n) perform the same; avoid char(n). node-postgres: numeric/bigint come back as strings, timestamp is interpreted in the process’s TZ.

References

• PostgreSQL 17 — Time Zones: timestamptz stores UTC, displayed per TimeZone.
• AT TIME ZONE: the two conversion directions.
• Current Date/Time: now() and clock_timestamp().
• Numeric Types: precision, speed, rounding rules.
• Character Types: performance, space padding, truncation on cast.
• node-postgres — Data Types: parsing TIMESTAMP per process.env.TZ.