Skip to content

How-to

How to Turn a CSV Column of Timestamps Into Local Times

Unix or ISO 8601 timestamps from an export, turned into readable times in your own zone, each row with its own daylight-saving offset. No upload, no script.

The problem

“My export has a created_at column full of numbers like 1719668707123. I need readable dates in my own time zone for thousands of rows, not one value at a time.”

Why exports look like this

Databases, payment providers, analytics tools and app backends store moments as numbers or as UTC text because that is exact and sorts correctly: 1719668707123 (Unix milliseconds) or 2024-06-29T13:45:07Z (ISO 8601). An export writes that stored value as it is. It is right for the next program and unreadable for people, and it is in UTC, not in the zone of the team reading the report.

Converting one cell is easy. Converting the column is where it gets slow: a spreadsheet formula per row, a script, or pasting values one by one into a converter. And a CSV export often carries customer names, emails or amounts next to the timestamps, which many people would rather not upload to a website to get readable dates.

Fix it on Date And Time Converter

  1. Check one value first. Paste a cell into the converter and confirm what it reads: for a number, the unit (seconds, milliseconds, microseconds); for text, whether it carries a Z or an offset.
  2. Open the CSV in the file workspace: drop it on the home page, choose it, or paste the rows. The file is read by your browser and stays on your device.
  3. Pick the timestamp column. The workspace proposes the format from the column’s first values; change it if your check in step 1 says otherwise.
  4. Choose the target format, for example YYYY-MM-DD HH:mm:ss, and the target zone, for example London or New York. Each row is converted with the offset in force on its own date, so rows from summer and winter both come out right.
  5. Read the preview of the first 8 rows and the counts for the whole file: how many rows converted, and how many were empty or could not be read.
Worked example: One cell of the column, as a date and time in London.
Input
1719668707123 read as Unix Timestamp (ms) (detected)
Result
2024-06-29 14:45:07 YYYY-MM-DD HH:mm:ss · YYYY-MM-DD HH:mm:ss · +01:00

Open in the converter: 1719668707123 → 2024-06-29 14:45:07

A whole column?

Open the CSV in the file workspace: pick the column, the formats and the zones, and the first 8 rows are converted at once, with counts for the whole file. The file is read in your browser and never uploaded. Downloading the converted file is part of Pro ($9.99/month).

Open the file workspace

With a formula or a script

In Excel, add a column next to the timestamps and fill it down (Unix milliseconds in column A, result in UTC):

=A2/86400000+DATE(1970,1,1)

Format the new column as yyyy-mm-dd hh:mm:ss. To show a fixed offset, add hours as a fraction of a day (+1/24 for one hour ahead), but a fixed offset is wrong for half the year anywhere that uses daylight saving. Excel has no time-zone rules; a zone-aware conversion needs a script or a converter that knows the zone’s history.

In pandas, the zone rules are built in:

import pandas as pd

df = pd.read_csv("export.csv", dtype={"created_at": "int64"})
df["created_local"] = (
    pd.to_datetime(df["created_at"], unit="ms", utc=True)
      .dt.tz_convert("Europe/London")
      .dt.strftime("%Y-%m-%d %H:%M:%S")
)
df.to_csv("export_readable.csv", index=False)

Pitfalls

  • Mixed units in one column. A seconds value read as milliseconds is still a valid moment, in January 1970, so nothing refuses it. If the earliest converted dates are in 1970, the column mixes units: separate the rows by length and convert each unit in its own run.
  • Opening the export in Excel first. Excel shows a 13-digit number in scientific notation (1.71967E+12), cuts anything past 15 digits, and may rewrite ISO text when it saves. Convert the file as exported.
  • Text timestamps without a zone. 2024-06-29 13:45:07 says nothing about its zone. Find out where it was written (often UTC, sometimes the server’s local time) and set that as the source zone before converting.
  • Blank cells. They stay blank and are counted separately, so a column with gaps is not reported as an error.

Questions

Is my file uploaded?

No. The file is read by your browser and converted on your device; its contents are never sent to a server.

What can I do without paying?

Everyone can open a file, pick the column and formats, see the first 8 rows converted and get counts for the whole file. Downloading the converted file is part of Pro.

Can I convert two date columns?

One column per run. Convert the first column, download the converted file (downloading is part of Pro), then open that file and convert the second column.

What happens to rows that are not timestamps?

They are kept as they were and counted by kind (could not be read, empty, needs a decision). The free preview shows each of its 8 rows’ status; after a Pro run the report lists every such row.