Writing Dates and Booleans to CSV That Others Can Read

A CSV has no types. A date is whatever characters the exporter chose, and a boolean is whatever it felt like. Both choices usually depend on settings you did not pass on the command line: a session time zone, a locale, a date style, the engine's own idea of a boolean. The result is a file that reads correctly on your machine and wrongly on another, or reads without an error and means something different. This article shows each of those settings changing the output, shows how pandas misreads one of them, and ends with a short list of formats to write explicitly.

The same instant, three files

In PostgreSQL, a timestamptz is an instant. How it is printed depends on the session's TimeZone setting, and the setting comes from the client or the server's default. The same export, run with three settings:

for tz in UTC Asia/Ho_Chi_Minh America/New_York; do
  echo "TimeZone = $tz"; PGOPTIONS="-c TimeZone=$tz" psql --csv -c "SELECT id, joined FROM ex_people WHERE id IN (1, 6) ORDER BY id"
done

TimeZone = UTC
id,joined
1,2025-03-01 09:30:00+00
6,
TimeZone = Asia/Ho_Chi_Minh
id,joined
1,2025-03-01 16:30:00+07
6,
TimeZone = America/New_York
id,joined
1,2025-03-01 04:30:00-05
6,

2025-03-01 09:30:00+00, 2025-03-01 16:30:00+07 and 2025-03-01 04:30:00-05. All three are correct, and all three are the same moment. A consumer that reads the offset gets the right instant. A consumer that drops the offset, which is what a lot of loaders and spreadsheet imports do, gets a different wall-clock time depending on which machine ran the export. And a job that moves from a laptop to a server with a different default has just changed its output without anyone touching the code. The PostgreSQL documentation on time zones explains how the session setting is chosen.

The date style

The second hidden setting is DateStyle. With the default ISO, dates print as 2025-03-01. COPY uses the session's style:

psql -c "\copy (SELECT id, joined::date AS day, joined FROM ex_people WHERE id IN (1, 7) ORDER BY id) TO STDOUT WITH (FORMAT csv, HEADER)"
echo --- DateStyle German
PGOPTIONS="-c DateStyle=German" psql -c "\copy (SELECT id, joined::date AS day, joined FROM ex_people WHERE id IN (1, 7) ORDER BY id) TO STDOUT WITH (FORMAT csv, HEADER)"
echo --- DateStyle SQL, DMY
PGOPTIONS="-c DateStyle=SQL,DMY" psql -c "\copy (SELECT id, joined::date AS day, joined FROM ex_people WHERE id IN (1, 7) ORDER BY id) TO STDOUT WITH (FORMAT csv, HEADER)"

id,day,joined
1,2025-03-01,2025-03-01 09:30:00+00
7,2025-03-07,2025-03-07 17:45:00+00
--- DateStyle German
id,day,joined
1,01.03.2025,01.03.2025 09:30:00 UTC
7,07.03.2025,07.03.2025 17:45:00 UTC
--- DateStyle SQL, DMY
id,day,joined
1,01/03/2025,01/03/2025 09:30:00 UTC
7,07/03/2025,07/03/2025 17:45:00 UTC

Under DateStyle=German the same row came out as 01.03.2025 and 01.03.2025 09:30:00 UTC, and under SQL,DMY as 01/03/2025. Nobody picks those on purpose for an export, but a server or a role configured for a German or British application will have them set, and COPY honors them. The new thing in the German output is UTC as a time zone name instead of +00.

What does a consumer do with it? I fed four spellings to pandas with parse_dates:

python - <<'PY'
import io, pandas as pd
cases = {
  'postgres default':  "id,active,joined\n1,t,2025-03-01 09:30:00+00\n2,f,2025-03-02 23:59:59+00\n6,,\n",
  'true/false + ISO Z': "id,active,joined\n1,true,2025-03-01T09:30:00Z\n2,false,2025-03-02T23:59:59Z\n6,,\n",
  '1/0 + ISO Z':        "id,active,joined\n1,1,2025-03-01T09:30:00Z\n2,0,2025-03-02T23:59:59Z\n6,,\n",
  'German date style':  "id,active,joined\n1,t,01.03.2025 09:30:00+00\n2,f,02.03.2025 23:59:59+00\n6,,\n",
}
for name, text in cases.items():
    df = pd.read_csv(io.StringIO(text), parse_dates=['joined'])
    print(f"{name:20} active: {str(df.active.dtype):8} joined: {str(df.joined.dtype):28} first joined: {df.joined.iloc[0]}")
PY

<stdin>:9: UserWarning: Could not infer format, so each element will be parsed individually, falling back to `dateutil`. To ensure parsing is consistent and as-expected, please specify a format.
postgres default     active: str      joined: datetime64[us, UTC]          first joined: 2025-03-01 09:30:00+00:00
true/false + ISO Z   active: object   joined: datetime64[us, UTC]          first joined: 2025-03-01 09:30:00+00:00
1/0 + ISO Z          active: float64  joined: datetime64[us, UTC]          first joined: 2025-03-01 09:30:00+00:00
German date style    active: str      joined: datetime64[us, UTC]          first joined: 2025-01-03 09:30:00+00:00

The ISO spellings (2025-03-01 09:30:00+00 and 2025-03-01T09:30:00Z) parsed to 9:30 UTC on 1 March, as intended. The German spelling parsed as 2025-01-03: pandas fell back to dateutil (the warning says Could not infer format), which assumed month first, and read 1 March as 3 January. No error, a valid date and a wrong one. This is the failure that the date checking article is about, caused by the exporter and not by the data.

Fix the format in the query

Do not rely on session settings. Format the columns in the query, so the output depends on the SQL and not on the connection:

psql -c "\copy (SELECT id, CASE WHEN active THEN 'true' WHEN NOT active THEN 'false' END AS active, to_char(joined AT TIME ZONE 'UTC', 'YYYY-MM-DD\"T\"HH24:MI:SS\"Z\"') AS joined_utc FROM ex_people WHERE id IN (1, 2, 6) ORDER BY id) TO STDOUT WITH (FORMAT csv, HEADER)"
echo --- same output under another session time zone
PGOPTIONS="-c TimeZone=Asia/Ho_Chi_Minh" psql -c "\copy (SELECT id, CASE WHEN active THEN 'true' WHEN NOT active THEN 'false' END AS active, to_char(joined AT TIME ZONE 'UTC', 'YYYY-MM-DD\"T\"HH24:MI:SS\"Z\"') AS joined_utc FROM ex_people WHERE id IN (1, 2, 6) ORDER BY id) TO STDOUT WITH (FORMAT csv, HEADER)"

id,active,joined_utc
1,true,2025-03-01T09:30:00Z
2,false,2025-03-02T23:59:59Z
6,,
--- same output under another session time zone
id,active,joined_utc
1,true,2025-03-01T09:30:00Z
2,false,2025-03-02T23:59:59Z
6,,

to_char(joined AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS"Z"') converts the instant to UTC and prints it as ISO 8601 with a literal Z. The boolean is written as the words true and false with a CASE, and a NULL stays NULL (the empty third row). The second half of the output reran the identical query under the Asia/Ho_Chi_Minh time zone. Same bytes. That is the property you want: the export has no hidden inputs.

Two cautions. The literal Z is only honest after AT TIME ZONE 'UTC'; writing Z after a local time is a lie that every parser will believe. And to_char returns text, so a driver or a view that reads your query's result types sees strings, which does not matter for a CSV and matters if the same query feeds something else.

Booleans

Every engine spells a boolean differently:

echo "sqlite:"; sqlite3 -csv -header work/export/ex.sqlite "SELECT id, active, joined FROM ex_people WHERE id IN (1, 2, 6) ORDER BY id"
echo "duckdb:"; duckdb work/export/ex.duckdb -csv -c "SELECT id, active, joined FROM ex_people WHERE id IN (1, 2, 6) ORDER BY id"
echo "mongo (see the mongoexport article): true/false and 2025-03-01T09:30:00.000Z"

sqlite:
id,active,joined
1,1,"2025-03-01 09:30:00"
2,0,"2025-03-02 23:59:59"
6,,
duckdb:
id,active,joined
1,true,2025-03-01 09:30:00+00
2,false,2025-03-02 23:59:59+00
6,NULL,NULL
mongo (see the mongoexport article): true/false and 2025-03-01T09:30:00.000Z

PostgreSQL's COPY writes t and f, as in the psql article. SQLite stores 1 and 0 (it has no boolean type). DuckDB writes true and false, and mongoexport does the same. MySQL's boolean is tinyint(1), so it prints 1 and 0, as the MySQL block below shows.

Whether a reader understands them depends on the reader. In the pandas test, t/f with a missing value read as strings, true/false with a missing value read as object (a mix of Python booleans and a float NaN), and 1/0 with a missing value read as float64, so the integer 1 became 1.0. Only a column with no missing values would read as bool or int64. A boolean with three states is not a boolean for most data tools, and the export is the place to decide what the third state looks like. I write true, false and an empty field, and tell the reader to expect it. Another reasonable choice is to refuse NULLs in boolean columns upstream.

MySQL: DATETIME and TIMESTAMP are different

MySQL has two date-time types that look alike. DATETIME stores what you gave it. TIMESTAMP stores an instant and converts it to the session time zone on the way out:

mysql -h mysql -uroot -psecret lab --default-character-set=utf8mb4 -e "DROP TABLE IF EXISTS ex_times; CREATE TABLE ex_times (id int, dt datetime, ts timestamp NULL, flag boolean); SET time_zone = '+00:00'; INSERT INTO ex_times VALUES (1, '2025-03-01 09:30:00', '2025-03-01 09:30:00', true), (2, NULL, NULL, false)" 2>/dev/null
for tz in +00:00 +07:00; do echo "session time_zone $tz"; mysql -h mysql -uroot -psecret lab --default-character-set=utf8mb4 --batch -e "SET time_zone = '$tz'; SELECT id, dt, ts, flag FROM ex_times ORDER BY id" 2>/dev/null; done
mysql -h mysql -uroot -psecret lab --default-character-set=utf8mb4 --batch -e "SELECT id, flag, flag = true AS is_true, IF(flag, 'true', 'false') AS as_text, DATE_FORMAT(dt, '%Y-%m-%dT%H:%i:%sZ') AS iso FROM ex_times ORDER BY id" 2>/dev/null

session time_zone +00:00
id	dt	ts	flag
1	2025-03-01 09:30:00	2025-03-01 09:30:00	1
2	NULL	NULL	0
session time_zone +07:00
id	dt	ts	flag
1	2025-03-01 09:30:00	2025-03-01 16:30:00	1
2	NULL	NULL	0
id	flag	is_true	as_text	iso
1	1	1	true	2025-03-01T09:30:00Z
2	0	0	false	NULL

With the session set to +00:00, both columns printed 2025-03-01 09:30:00. With +07:00, the DATETIME still printed 09:30:00 and the TIMESTAMP printed 16:30:00. Neither prints an offset, so the CSV does not say which one you are looking at. The last query formats the DATETIME with DATE_FORMAT(dt, '%Y-%m-%dT%H:%i:%sZ'), which is correct only if the stored value is UTC, and IF(flag, 'true', 'false') for the boolean; a NULL date stays NULL through DATE_FORMAT. The MySQL documentation on time zone handling describes the conversion. If you cannot tell whether the stored values are UTC or local, find out before you export, because the file will not tell you.

What I write

A short list, kept in the contract with the provider or the consumer:

The numbers row comes from the numbers article, where the other side of the same failure is documented. All of the formats are in the query, not in the session.

How to tell it worked

Export with two different session settings (time zone, date style, locale) and compare the files byte for byte: they should be identical. Read the file with the consumer's own tool and look at the first and last rows, plus a row whose day is 12 or below, because a day-month swap shows up there and nowhere else. Choose a row with a NULL boolean and see what type the column becomes. And when the consumer is another team, ask them to read a sample before you export the whole table.

When the reader is a spreadsheet

All of this assumes a program reads the file. If a person opens it in a spreadsheet, the spreadsheet's own locale decides how it interprets a date-like text, and it may convert 2025-03-01 to a date, a time zone-less timestamp to a number, or a text such as 1-2 to a date. I did not test any spreadsheet here. The article on opening UTF-8 CSV files in Excel separates what I observed from what is only reported. If the reader is a spreadsheet, a quick test with the person who will open it is better than any rule I can write.