Athena Timestamp Format: Convert String to Timestamp or Date (date_parse, from_iso8601, parse_datetime), Time Zones, Date Ranges and Errors

Logeshwaran
—

Amazon Athena reads a timestamp directly only in the form yyyy-mm-dd hh:mm:ss (with optional fractions of a second), for example 2026-10-19 14:30:00. Anything else, ISO strings like 2026-10-19T14:30:00Z, US dates like 10/19/2026, or Unix epoch numbers, must be converted: from_iso8601_timestamp(col) for ISO 8601, date_parse(col, '%m/%d/%Y %H:%i:%s') for custom formats (MySQL-style codes, where %i is minutes), parse_datetime(col, 'dd/MM/yyyy HH:mm') for Java-style patterns, and from_unixtime(col) for epoch seconds (divide milliseconds by 1000). Convert a timestamp to a date with CAST(ts AS date) or date(ts), and to a string with date_format(ts, '%Y-%m-%d'). Compare against typed literals, ts >= TIMESTAMP '2026-10-01 00:00:00', not plain strings, or Athena refuses with "Cannot apply operator: timestamp(3) >= varchar". If a table defined with a timestamp column fails with HIVE_BAD_DATA ... Timestamp format must be yyyy-mm-dd hh:mm:ss[.fffffffff], define the column as string and convert in your query or a view. Below: every conversion with examples, the format codes table, time zones, date arithmetic, filtering by date ranges without losing partition pruning, and every timestamp error.

The shop's web server logs went into S3 every hour, and Jake wanted a simple answer: how many orders per day last week? His first query compared the log's time column with '2026-10-12' and failed with an operator error. His second, after Ethan changed the table to use a timestamp column, failed differently: Athena could not read the timestamps at all, because the logs wrote them as 2026-10-12T09:15:42Z, with a T and a Z. Two errors, one lesson: Athena is strict about what a timestamp looks like, and very good at converting once you tell it how.

⚡ Quick Answer

• ISO string to timestamp: from_iso8601_timestamp(col). All conversions.

• Custom format: date_parse(col, '%d/%m/%Y %H:%i'). Format codes.

• Timestamp to date or string: CAST(ts AS date), date_format(ts, '%Y-%m-%d'). Output.

• Filter by dates: ts >= TIMESTAMP '2026-10-01 00:00:00'. Ranges.

The accepted format · Time zones · Date math · Table design · Errors.

New to Athena? The cards below cover querying S3, what queries cost, HIVE_BAD_DATA errors and Lake Formation permissions.

🧭 NEW HERE? READ THESE FIRST

New to Athena or data lakes on S3? These five pages give you the background this one builds on:

📌 Bookmark this; the format codes table and the conversion cheat sheet are the parts you will come back for.

The one timestamp format Athena reads directly

Athena's SQL engine (based on Trino) has proper date and time types: date, timestamp (a date and time with no zone), and timestamp with time zone. Data in S3 is text, though, so somewhere the text has to become a timestamp. Athena does that directly only for one shape:

2026-10-19 14:30:00
2026-10-19 14:30:00.123

Year, month and day with hyphens, a space, then hours, minutes and seconds with colons, and optionally fractions of a second. That is what CAST('2026-10-19 14:30:00' AS timestamp) accepts, and what a table column declared as timestamp expects to find in CSV or text files. Everything else needs a function, and the most common culprits are:

  • ISO 8601 with a T and a zone: 2026-10-19T14:30:00Z, 2026-10-19T14:30:00+05:30 (APIs, JSON logs, CloudTrail).
  • Regional date orders: 10/19/2026 (US) or 19/10/2026 (UK, India, Australia).
  • Unix epoch numbers: 1760884200 seconds, or 1760884200123 milliseconds (VPC flow logs, many app logs).
  • Month names: 19-Oct-2026 14:30, or Apache-style 19/Oct/2026:14:30:00 +0000.

Jake: "So the T in the middle is what broke it?"

Ethan: "The T and the Z. One function call fixes both."

Convert a string to a timestamp: every case

-- Already 'yyyy-mm-dd hh:mm:ss'
SELECT CAST('2026-10-19 14:30:00' AS timestamp);

-- ISO 8601: T separator, Z or +05:30 offset  (returns timestamp with time zone)
SELECT from_iso8601_timestamp('2026-10-19T14:30:00Z');

-- ISO date only
SELECT from_iso8601_date('2026-10-19');

-- Custom format with MySQL-style codes (%i = minutes!)
SELECT date_parse('19/10/2026 14:30', '%d/%m/%Y %H:%i');
SELECT date_parse('10/19/2026 02:30 PM', '%m/%d/%Y %h:%i %p');

-- Custom format with Java-style patterns
SELECT parse_datetime('19-Oct-2026 14:30', 'dd-MMM-yyyy HH:mm');

-- Apache access log time: 19/Oct/2026:14:30:00 +0000
SELECT parse_datetime('19/Oct/2026:14:30:00 +0000', 'dd/MMM/yyyy:HH:mm:ss Z');

-- Unix epoch seconds, and milliseconds
SELECT from_unixtime(1760884200);
SELECT from_unixtime(1760884200123 / 1000.0);

Match your input to the function:

Your text looks likeUseYou get
2026-10-19 14:30:00CAST(col AS timestamp)timestamp
2026-10-19T14:30:00Zfrom_iso8601_timestamp(col)timestamp with time zone
2026-10-19CAST(col AS date) or from_iso8601_date(col)date
19/10/2026 14:30date_parse(col, '%d/%m/%Y %H:%i')timestamp
10/19/2026 02:30 PMdate_parse(col, '%m/%d/%Y %h:%i %p')timestamp
19/Oct/2026:14:30:00 +0000parse_datetime(col, 'dd/MMM/yyyy:HH:mm:ss Z')timestamp with time zone
1760884200 (seconds)from_unixtime(col)timestamp (UTC)
1760884200123 (milliseconds)from_unixtime(col / 1000.0)timestamp (UTC)

Which one to use, and why:

  • from_iso8601_timestamp for anything ISO-shaped: it understands the T, fractions and every zone form, and returns a timestamp with time zone.
  • date_parse when the format is fixed and you know it: fast and clear, with the codes in the next section. It returns a plain timestamp.
  • parse_datetime when you think in Java/Joda patterns (yyyy-MM-dd HH:mm) or the text includes a zone offset to keep.
  • from_unixtime for epoch numbers, which are always UTC. Columns in milliseconds must be divided by 1000 first, or you get a date tens of thousands of years in the future.

When some of your rows are messy, wrap the conversion in try(): try(date_parse(col, '%d/%m/%Y')) returns NULL for rows that do not match instead of failing your whole query, and coalesce(try(...), try(...)) tries several formats in turn.

Converting a column you have never seen before, in four steps:

  1. Look at real values: SELECT DISTINCT col FROM t LIMIT 20, so you see the actual format, not the one you expect.
  2. Pick the function from the table above and test it on one literal value in a SELECT with no table.
  3. Find the rows that fail: SELECT col FROM t WHERE try(date_parse(col, '...')) IS NULL LIMIT 20.
  4. Handle the stragglers with coalesce and a second format, then put the final expression in a view so you only write it once.

date_parse and date_format codes

date_parse and date_format use MySQL-style codes. These are the ones you will use, and the one that catches nearly everyone is %i for minutes (%m is month):

CodeMeaningExample
%YFour-digit year2026
%yTwo-digit year26
%mMonth, 01 to 1210
%b / %MShort / full month nameOct / October
%dDay of month, 01 to 3119
%HHour, 00 to 2314
%h + %pHour 01 to 12, with AM/PM02 PM
%iMinutes, 00 to 5930
%sSeconds, 00 to 5900
%fFraction of a second123000
%a / %WShort / full weekday nameMon / Monday
%TTime as %H:%i:%s14:30:00

Literal characters in the text (slashes, a T, a Z) go into the format as they are: date_parse('2026-10-19T14:30:00Z', '%Y-%m-%dT%H:%i:%sZ') works, though from_iso8601_timestamp is simpler for that case. parse_datetime and format_datetime use Java-style patterns instead: yyyy, MM (month), dd, HH, mm (minutes), ss, MMM (Oct), Z (offset). Mixing up the two styles is the second most common mistake after %m for minutes.

Convert a timestamp to a date or a string

-- Timestamp to date
SELECT CAST(order_ts AS date) FROM orders;
SELECT date(order_ts) FROM orders;

-- Timestamp to string in your own format
SELECT date_format(order_ts, '%Y-%m-%d %H:%i') FROM orders;   -- 2026-10-19 14:30
SELECT date_format(order_ts, '%d %b %Y') FROM orders;          -- 19 Oct 2026
SELECT format_datetime(order_ts, 'yyyy-MM-dd') FROM orders;    -- Java-style

-- Timestamp to Unix seconds
SELECT to_unixtime(order_ts) FROM orders;

-- Today, now, and a date literal
SELECT current_date, current_timestamp, DATE '2026-10-19';

-- Timestamp to Unix milliseconds, for APIs and JavaScript
SELECT CAST(to_unixtime(order_ts) * 1000 AS bigint) FROM orders;

to_unixtime returns seconds as a decimal number, with milliseconds after the point. Multiply by 1000 and cast to bigint when another system, such as a JavaScript chart or a REST API, expects whole epoch milliseconds.

The difference between the two outputs matters for what you do next. A date (from CAST or date()) is still a typed value: you can compare it, group by it and do date arithmetic on it. A string from date_format is just text for display or export; it sorts correctly only if your format is year-first, like %Y-%m-%d. So group and filter on dates and timestamps, and use date_format only in the final SELECT list, where you shape the output for your report or spreadsheet.

Orders per day, which was Jake's actual question, is a conversion and a GROUP BY:

SELECT date(from_iso8601_timestamp(event_time)) AS day,
       count(*) AS orders
FROM web_logs
WHERE dt BETWEEN '2026-10-12' AND '2026-10-18'
  AND event = 'order_placed'
GROUP BY 1
ORDER BY 1;

Filtering by dates and times

Compare timestamps with timestamps and dates with dates. A plain string next to a timestamp column fails with an error such as "Cannot apply operator: timestamp(3) >= varchar(10)":

-- Fails: timestamp compared with a string
WHERE order_ts >= '2026-10-01'

-- Works: typed literals
WHERE order_ts >= TIMESTAMP '2026-10-01 00:00:00'
  AND order_ts <  TIMESTAMP '2026-11-01 00:00:00'

-- Works: whole days by date
WHERE CAST(order_ts AS date) BETWEEN DATE '2026-10-01' AND DATE '2026-10-31'

-- Last 7 days
WHERE order_ts >= current_timestamp - INTERVAL '7' DAY

A half-open range (>= the start, < the next start) is safer than BETWEEN for timestamps: BETWEEN ... AND TIMESTAMP '2026-10-31 00:00:00' silently drops everything after midnight on the 31st.

Keep partition pruning. If your table is partitioned by a string column such as dt='2026-10-19', filter that column directly with strings, as in the orders-per-day query. Wrapping a partition column in a function, for example date_parse(dt, '%Y-%m-%d') >= ..., can stop Athena from skipping partitions, so it reads every day of data and bills you for it. Filter the partition column as text, and convert the event-time column only for display or finer filtering. Our Athena partitioning guide shows how much that saves.

When partition dates and event times disagree

For the Hive-style tables most people use in Athena, the engine knows nothing about how the dt partition relates to the timestamp inside each row. That has two consequences worth planning for.

Filter on both. To get events between 11 p.m. on the 18th and 3 a.m. on the 19th, put the precise range on the timestamp column and a matching range on the partition column, which lets Athena skip every other day's files:

WHERE event_ts >= TIMESTAMP '2026-10-18 23:00:00'
  AND event_ts <  TIMESTAMP '2026-10-19 03:00:00'
  AND dt BETWEEN '2026-10-18' AND '2026-10-19'

Partitions may be by arrival, not by event. Data delivered by Amazon Data Firehose with its default settings is filed under the time it arrived, not the time inside the record. An order placed at 11:59 p.m. can land in the next day's folder, and late-arriving mobile events can land days later. If your counts at day boundaries look slightly off, widen the partition filter by a day on each side and let the timestamp condition do the exact cut. To file records by their own time, Firehose's dynamic partitioning can read the timestamp from each record.

Iceberg tables are smarter. An Iceberg table partitioned by a transform such as day(event_ts) prunes partitions from the timestamp condition alone, so you write one filter instead of two. For the best pruning, use whole-day boundaries (>= TIMESTAMP '2026-10-18 00:00:00' and < TIMESTAMP '2026-10-19 00:00:00'); a range that ends at noon cannot be translated into a single day partition, and Athena reads more than it needs to.

One more table-design point: keep date partition columns as string rather than date. Athena recognizes YYYY-MM-DD values as dates when the column is typed that way, but string partition keys perform better, and the text comparisons shown above sort correctly as long as the format is year first.

Daily partitions without MSCK REPAIR: partition projection

A table partitioned by day needs every new day registered before Athena can see it, which is why so many setups run MSCK REPAIR TABLE or a Glue crawler on a schedule, and why "yesterday's data is missing" is such a common complaint. Partition projection removes the step: you describe the date pattern once in the table properties, and Athena works out the partitions from the query itself.

ALTER TABLE web_logs SET TBLPROPERTIES (
  'projection.enabled'          = 'true',
  'projection.dt.type'          = 'date',
  'projection.dt.format'        = 'yyyy-MM-dd',
  'projection.dt.range'         = '2024-01-01,NOW',
  'projection.dt.interval'      = '1',
  'projection.dt.interval.unit' = 'DAYS',
  'storage.location.template'   = 's3://shop-logs/web/dt=${dt}/'
);

From then on, a query with WHERE dt BETWEEN '2026-10-12' AND '2026-10-18' reads exactly those seven folders, including today's the moment its first file lands, with no repair command and no crawler. The NOW in the range keeps the table current forever.

Two things to get right. The projection.dt.format uses Java-style patterns (yyyy-MM-dd), the same family as parse_datetime, not the %Y codes of date_parse. And queries must filter on the projected column: without a dt condition, Athena projects every day in the range and checks each folder, which is slow on a range spanning years. Our partitioning guide covers projection for other partition types.

Jake: "So no more 'run the crawler' before Monday's report?"

Ethan: "The table knows the folders are named by date. It finds today's by itself."

Time zones

Your plain timestamp values have no zone; Athena treats them as they are, so you need to know what zone your data was written in. from_iso8601_timestamp and parse_datetime with a Z keep the zone (timestamp with time zone). Epoch values from from_unixtime are UTC. To show or group by local time:

-- UTC event time shown in New York time
SELECT from_iso8601_timestamp(event_time) AT TIME ZONE 'America/New_York' FROM web_logs;

-- A plain timestamp you know is UTC, made zone-aware, then converted
SELECT with_timezone(order_ts, 'UTC') AT TIME ZONE 'Asia/Kolkata' FROM orders;

-- Daily totals by local day, not UTC day
SELECT date(from_iso8601_timestamp(event_time) AT TIME ZONE 'Europe/London') AS local_day, count(*)
FROM web_logs GROUP BY 1;

Use IANA zone names (America/New_York, Europe/London, Australia/Sydney, Asia/Kolkata) rather than fixed offsets, so daylight saving time is handled for you. Grouping by UTC day when your business runs in another zone is the usual cause of "the daily numbers don't match the dashboard".

Athena runs queries in UTC, which is what current_timezone() returns, so current_timestamp and current_date are UTC values too. "Today" at 1 a.m. in Sydney is still yesterday in UTC. For a local "today," convert first: date(current_timestamp AT TIME ZONE 'Australia/Sydney').

To go back from a zone-aware value to a plain timestamp, for a table column or a join, decide which clock you want and cast after converting: CAST(ts_tz AT TIME ZONE 'UTC' AS timestamp) gives the UTC wall-clock time, and the same with a local zone name gives local wall-clock time. Casting without AT TIME ZONE keeps whatever zone the value already had, which is easy to misread later.

Coming from another database? The function name map

Half of all "function not registered" errors in Athena are a familiar function from SQL Server, Oracle, PostgreSQL or MySQL that Athena spells differently. The common ones, side by side:

You know it asFromIn Athena
TO_DATE(s, 'YYYY-MM-DD')Oracle, PostgreSQLdate_parse(s, '%Y-%m-%d') or CAST(s AS date)
STR_TO_DATE(s, fmt)MySQLdate_parse(s, fmt), same codes
TO_CHAR(ts, fmt)Oracle, PostgreSQLdate_format(ts, fmt)
DATEADD(day, 7, ts)SQL Serverdate_add('day', 7, ts)
DATEDIFF(day, a, b)SQL Serverdate_diff('day', a, b)
GETDATE(), SYSDATESQL Server, Oraclecurrent_timestamp or now()
EXTRACT(YEAR FROM ts)Standard SQLWorks as written, or year(ts)
UNIX_TIMESTAMP(ts)MySQLto_unixtime(ts)

Watch the format strings when you move a query over: Oracle and PostgreSQL patterns such as 'YYYY-MM-DD HH24:MI' must be rewritten as '%Y-%m-%d %H:%i' for date_parse and date_format, or as 'yyyy-MM-dd HH:mm' for parse_datetime. The function name changes are easy; the format strings are where translated queries quietly return wrong dates.

Daylight saving time: the 23-hour and 25-hour days

Once you report in a local zone, daylight saving time shows up twice a year, and it surprises people every time. In a zone that observes it, one local day each spring has only 23 hours and one day each autumn has 25. Grouping by local hour then shows a missing hour in spring (in New York, there is no 2 a.m. on that Sunday) and a doubled hour in autumn, when 1 a.m. happens twice.

None of this is an error in your query; it is what the local clock really did. Three habits keep reports honest:

  • Measure durations in UTC. date_diff between two UTC timestamps gives the real elapsed time. Between two local wall-clock times that span a change, it can be an hour out.
  • Store UTC, convert for display. Data written as local times without a zone is ambiguous for the repeated autumn hour: two different moments have the same text. Writing UTC (or local time with its offset) avoids that for good.
  • Expect odd totals on change days. A daily report in local time shows slightly fewer orders on the 23-hour day and more on the 25-hour day. Mention it in the report rather than chasing it as a bug.

Using IANA zone names, as in the time zone section, is what makes Athena apply these rules correctly; a fixed offset such as -05:00 is wrong for half the year in a zone with daylight saving time.

Date arithmetic: date_add, date_diff, date_trunc

SELECT date_add('day', 7, order_ts);                 -- a week later
SELECT date_add('month', -1, current_date);          -- a month ago
SELECT order_ts + INTERVAL '2' HOUR;                 -- the same, with an interval
SELECT date_diff('minute', created_ts, shipped_ts);  -- minutes between two timestamps
SELECT date_diff('day', DATE '2026-10-01', current_date);
SELECT date_trunc('hour', order_ts);                 -- 14:37 becomes 14:00
SELECT date_trunc('week', order_ts);                 -- Monday of that week
SELECT day_of_week(order_ts), hour(order_ts), month(order_ts), year(order_ts);

date_trunc is the tool you want for "per hour" and "per week" reports: group by date_trunc('hour', ts) and every one of your rows lands in its hour. date_diff answers "how long did it take" questions, such as minutes from order to shipment, and you can average it per day for a simple service-level report. Units are given as strings: 'second', 'minute', 'hour', 'day', 'week', 'month', 'quarter', 'year'.

Two behaviors to know. date_diff counts whole units, so 23 hours is 0 days, and from 11 p.m. to 1 a.m. the next day is still 0 days although the calendar date changed; for "calendar days between," compare date() values instead. And weeks start on Monday: date_trunc('week', ts) returns the Monday, day_of_week numbers Monday as 1 and Sunday as 7, and week(ts) gives the ISO week number, which can belong to the previous or next year around New Year (year_of_week(ts) gives the matching year).

Jake: "So for orders per hour it's date_trunc, and for per day it's just date()?"

Ethan: "Both work for days. Pick one and use it everywhere, so the reports agree."

Today, yesterday, this month: a cheat sheet

Report queries keep needing the same handful of relative dates. Here they are in one place, all built from current_date (remember it is the UTC date):

SELECT
  current_date                                         AS today,
  current_date - INTERVAL '1' DAY                      AS yesterday,
  date_trunc('week', current_date)                     AS this_monday,
  date_trunc('month', current_date)                    AS first_of_this_month,
  last_day_of_month(current_date)                      AS last_of_this_month,
  date_trunc('month', current_date) - INTERVAL '1' MONTH AS first_of_last_month,
  date_trunc('year', current_date)                     AS first_of_this_year;

The most useful filter shape built from these is "last month, whole":

WHERE order_ts >= CAST(date_trunc('month', current_date) - INTERVAL '1' MONTH AS timestamp)
  AND order_ts <  CAST(date_trunc('month', current_date) AS timestamp)

It works in every month, including February and leap years, with no hard-coded dates, which makes it right for a scheduled report. For a partitioned table, add the matching text filter on dt, built with date_format(date_trunc('month', current_date) - INTERVAL '1' MONTH, '%Y-%m-%d'), so Athena still skips the other months.

If your reports run on a schedule early in the morning, decide once whether "yesterday" means the UTC day or your local day, and build every query the same way. A report emailed at 7 a.m. in Sydney that uses the UTC date covers a day that ended at 11 a.m. local time the previous day, which is rarely what the reader assumes.

Show days with zero orders: a calendar with sequence()

A GROUP BY only returns days that have rows. If nothing sold on a Sunday, that Sunday is simply missing from the result, and a chart built from it quietly joins Saturday to Monday. The fix is to generate the full list of days and join your counts to it:

WITH days AS (
  SELECT day
  FROM UNNEST(sequence(DATE '2026-10-01', DATE '2026-10-31', INTERVAL '1' DAY)) AS t(day)
),
orders_per_day AS (
  SELECT date(from_iso8601_timestamp(event_time)) AS day, count(*) AS orders
  FROM web_logs
  WHERE dt BETWEEN '2026-10-01' AND '2026-10-31' AND event = 'order_placed'
  GROUP BY 1
)
SELECT d.day, coalesce(o.orders, 0) AS orders
FROM days d
LEFT JOIN orders_per_day o ON o.day = d.day
ORDER BY d.day;

sequence builds an array of dates from the start to the end in steps of the interval, and UNNEST turns that array into rows. The LEFT JOIN keeps every day, and coalesce turns the missing counts into zeros. The same pattern works per hour with timestamps and INTERVAL '1' HOUR, as long as the range is not enormous: sequence is limited to 10,000 entries.

Designing the table: string column or timestamp column?

When the files' timestamps are not in Athena's native format, declare the column as string and convert in queries, or, better, in a view so nobody has to remember the function:

CREATE OR REPLACE VIEW web_logs_v AS
SELECT from_iso8601_timestamp(event_time) AS event_ts,
       event, user_id, dt
FROM web_logs;

SELECT date_trunc('hour', event_ts) AS hour, count(*) AS events
FROM web_logs_v
WHERE dt = '2026-10-19'
GROUP BY 1 ORDER BY 1;

Better still, for data you query often, convert once. Moving your text logs to a typed, partitioned copy:

  1. Write the conversion as a SELECT that returns a real timestamp column (and your partition column, such as dt).
  2. Run it as a CTAS query: CREATE TABLE web_logs_parquet WITH (format = 'PARQUET', partitioned_by = ARRAY['dt'], external_location = 's3://shop-lake/web_logs_parquet/') AS SELECT ....
  3. Check a few rows and counts against the original table.
  4. Point your reports and views at the new table.
  5. Load new days with INSERT INTO the same table, from a scheduled query or a Glue job.

A Glue job or an Athena CTAS query writes the data back to S3 as Parquet with a real timestamp column, partitioned by date. Parquet stores timestamps as typed values, so there is no text to parse, and queries get faster and cheaper. If a Glue job doing that conversion runs out of memory on a large dataset, our Glue OutOfMemoryError guide covers the fixes.

For JSON data, the same rule applies: keep ISO strings as string in the table definition and convert with from_iso8601_timestamp, unless you rewrite the data to Parquet.

Timestamps buried inside JSON columns

Sometimes the whole event sits in one string column as raw JSON, and the time is a field inside it. Pull the field out first, then convert it as usual:

SELECT from_iso8601_timestamp(json_extract_scalar(payload, '$.created_at')) AS created_ts,
       from_unixtime(CAST(json_extract_scalar(payload, '$.ts_ms') AS bigint) / 1000.0) AS ts_from_epoch
FROM raw_events
WHERE dt = '2026-10-19';

json_extract_scalar returns the value as text, which is exactly what the conversion functions want, and returns NULL when the field is missing, so a row without the field does not fail the query. Epoch values inside JSON come back as text too, so cast them to bigint before dividing. If the payload is already a json value rather than a string, the same functions work; json_parse(payload) turns a string into one when you need the other JSON functions.

Extracting from JSON on every query costs scanning time, because Athena reads the whole payload each time. If a report runs daily, the CTAS conversion above, with created_ts as a real column, pays for itself quickly.

"Athena file modified time": $path, $file_modified_time and friends

Every Hive and Iceberg table in Athena has hidden columns describing the files behind each row. They do not appear in SELECT *, but you can name them, in double quotes:

-- Which file did each row come from, and when was that file written?
SELECT "$path", "$file_modified_time", "$file_size", count(*) AS rows_in_file
FROM web_logs
WHERE dt = '2026-10-19'
GROUP BY 1, 2, 3
ORDER BY "$file_modified_time" DESC;

This is handy for questions the data itself cannot answer: which file holds the bad row behind a HIVE_BAD_DATA error, whether last night's export arrived, or how late the latest file landed. "$file_modified_time" is the S3 object's last-modified time as a timestamp with time zone, so all the functions on this page apply to it, and filtering on "$path" lets you query one file alone. These hidden columns work on tables, not on views.

A practical use is a freshness check for a scheduled report: SELECT max("$file_modified_time") FROM web_logs WHERE dt = date_format(current_date, '%Y-%m-%d') tells you when the newest file for today arrived. If it is hours old, the problem is upstream, in the job that writes the logs, not in your query. Note that the modified time is when the file was written to S3, not when the events inside it happened.

Milliseconds, microseconds and timestamp(3)

The timestamp(3) in Athena's error messages is the precision: three digits after the second, so milliseconds. That is the precision Athena reads and writes for Hive tables, and it shows up in a few places:

  • Iceberg tables can store microseconds, but Athena supports only millisecond precision for timestamps, both reading and writing, so the last three digits are not kept when Athena rewrites data.
  • Text data with more digits, such as 14:30:00.123456, is read to the millisecond; if you need every digit for matching or ordering, keep the original string alongside the converted column.
  • CSV tables using OpenCSVSerDe are the odd one out: a column declared timestamp there expects epoch milliseconds in the file, not a formatted date, so declare formatted timestamps as string and convert in the query.
  • Literals and comparisons carry precision too: TIMESTAMP '2026-10-19 14:30:00' is a timestamp(0), and Athena converts between precisions automatically when comparing, so you rarely need to think about it beyond reading the error messages correctly.

Worked examples: the timestamps in AWS's own logs

Many Athena users are querying logs that AWS services write, and each service uses its own time format. The conversions you need for the common ones:

  • CloudTrail: eventtime is an ISO string such as 2026-10-19T14:30:00Z, so from_iso8601_timestamp(eventtime). Because every value is UTC with the same layout, a plain text filter like eventtime >= '2026-10-19T00:00:00Z' also works and is cheap.
  • Application Load Balancer access logs: the time field is ISO 8601 with microseconds and a Z, which from_iso8601_timestamp reads directly; the extra digits beyond milliseconds are not kept.
  • S3 server access logs: requestdatetime looks like 19/Oct/2026:14:30:00 +0000, the Apache style, so parse_datetime(requestdatetime, 'dd/MMM/yyyy:HH:mm:ss Z').
  • VPC Flow Logs: start and end are Unix epoch seconds, so from_unixtime(start); our VPC Flow Logs guide has ready-made queries.
  • CloudWatch Logs exported to S3: each line starts with an ISO timestamp, and the event's own JSON often carries another, sometimes in a different zone. Decide which one your report means before grouping.

The text-filter trick in the CloudTrail item deserves a note. ISO strings in UTC with a fixed layout sort in the same order as the times they represent, so comparing them as text gives correct results and skips the conversion for every row. It breaks as soon as values mix zones (Z and +05:30) or layouts (with and without fractions), so use it only on data you know is uniform, and convert in the SELECT list for display.

Fast, cheap timestamp queries

Athena bills by data scanned, and timestamp handling has a big effect on how much it scans. In rough order of impact:

  • Filter the partition column as text. Covered above, and it matters most: one day instead of two years is a several-hundred-fold saving.
  • Store a real timestamp in Parquet. Parquet keeps minimum and maximum values for each block of rows, so a filter on a typed timestamp column lets Athena skip whole blocks inside a file. A string column holding the same times gets far less benefit, and needs a conversion on every row as well.
  • Convert once, in a view or a CTAS table, rather than repeating different conversions in every report, which also keeps everyone's numbers consistent.
  • Select only the columns you need. With Parquet, Athena reads only those columns, so SELECT event_ts, event scans a fraction of SELECT *.
  • Sort or cluster data by time when you write it, so each file covers a narrow time range and the block-skipping works well.

For Jake's shop, moving the web logs from text to Parquet with a real timestamp and daily partitions cut the weekly report's scan from about 40 GB to under 300 MB, and the bill for it from cents to fractions of a cent. At larger volumes the same change is the difference between a cheap report and an expensive one.

Getting dates into Excel and dashboards cleanly

The last step of many Athena queries is a spreadsheet or a dashboard, and dates are where that step goes wrong. Athena's result files are CSV, and how a date arrives depends on its type in your SELECT:

  • For Excel, output dates as date_format(ts, '%Y-%m-%d %H:%i:%s') or plain date(ts). Excel recognizes year-first values with a space as dates, while ISO strings with a T and Z often stay as text that will not sort or filter as dates.
  • Say which zone the times are in, in the column name (order_time_london) or a header row. A spreadsheet has no idea, and readers assume their own.
  • For dashboards that connect to Athena directly, such as Amazon QuickSight, return real date and timestamp values rather than formatted strings. The tool then recognizes them as dates and offers date filters and drill-downs; strings arrive as text and lose all of that.
  • Avoid locale-dependent formats like 10/19/2026 in exports. Opened by someone with UK settings, the 10th of the 19th month is not a date at all, and early days of the month get silently swapped.

Jake: "So the spreadsheet showed the dates as text because of the T?"

Ethan: "Exactly. I format them with a space in the query now, and Excel treats them as dates."

A checklist for any new timestamp column

  1. Look at real values with SELECT DISTINCT col ... LIMIT 20, including rows from the start and end of the data, since formats sometimes change over time.
  2. Identify the zone: a Z or offset in the text, UTC epochs, or local times with no zone at all, which you must find out from whoever writes the data.
  3. Choose the function from the conversion table and test it on literals first.
  4. Find failing rows with try() and handle them with coalesce and a second format.
  5. Put the conversion in a view, or in a Parquet table via CTAS for data you query often.
  6. Decide the reporting zone and apply AT TIME ZONE before grouping by day or hour.
  7. Filter partitions as text and the timestamp with typed literals, in every query.

Seven steps sounds like a lot for one column, but each takes a minute, and together they prevent every error in the next section.

Athena timestamp errors, fixed

  • HIVE_BAD_DATA: Error parsing field value ... Timestamp format must be yyyy-mm-dd hh:mm:ss[.fffffffff]: the table says timestamp but the files hold another format. Change the column to string and convert in the query or a view. Our HIVE_BAD_DATA guide covers the other causes of that error.
  • Cannot apply operator: timestamp(3) >= varchar: comparing a timestamp with a string. Use TIMESTAMP '...' or DATE '...' literals, or convert the string.
  • INVALID_FUNCTION_ARGUMENT: Invalid format ... is malformed: the text does not match the pattern you gave date_parse or parse_datetime. Check day and month order, %i vs %m, and literal characters; use try() for rows that vary.
  • INVALID_CAST_ARGUMENT: Value cannot be cast to timestamp: CAST on a string that is not yyyy-mm-dd hh:mm:ss. Use one of the parsing functions instead.
  • Dates in the year 57,000-something: epoch milliseconds passed to from_unixtime without dividing by 1000.
  • Times off by a few hours: UTC data shown or grouped as if it were local time. Convert with AT TIME ZONE.
  • Function not registered: a function name from another database, such as to_date or str_to_date, which Athena does not have. Use date_parse, CAST or date().

If a query fails before it reaches the data with a permissions error instead, our Lake Formation guide covers "Insufficient Lake Formation permission(s)". And for Unix-epoch log data in practice, our VPC Flow Logs guide has ready-made Athena queries.

Frequently asked questions

What timestamp format does Athena use?

yyyy-mm-dd hh:mm:ss with optional fractions, such as 2026-10-19 14:30:00. Other formats must be converted with functions.

How do I convert a string to a timestamp in Athena?

Use from_iso8601_timestamp for ISO strings, date_parse with a format for custom strings, parse_datetime for Java-style patterns, or CAST for yyyy-mm-dd hh:mm:ss.

How do I convert a timestamp to a date in Athena?

CAST(ts AS date) or date(ts).

How do I convert a timestamp to a string in Athena?

date_format(ts, '%Y-%m-%d %H:%i') with MySQL-style codes, or format_datetime(ts, 'yyyy-MM-dd') with Java-style patterns.

What is the format code for minutes in date_parse?

%i. %m is the month, which is the most common mistake.

How do I get the current date in Athena?

current_date for today's date and current_timestamp (or now()) for the current date and time.

How do I convert a Unix timestamp in Athena?

from_unixtime(seconds); for milliseconds, divide by 1000 first: from_unixtime(ms / 1000.0).

How do I query between two dates in Athena?

Use typed literals: ts >= TIMESTAMP '2026-10-01 00:00:00' AND ts < TIMESTAMP '2026-11-01 00:00:00'.

Why does comparing a timestamp with a string fail in Athena?

Athena does not compare timestamp and varchar implicitly. Use TIMESTAMP or DATE literals, or convert the string.

How do I convert time zones in Athena?

Use AT TIME ZONE 'America/New_York' on a timestamp with time zone, or with_timezone(ts, 'UTC') first for a plain timestamp.

What does HIVE_BAD_DATA Timestamp format must be yyyy-mm-dd hh:mm:ss mean?

The table declares a timestamp column but the files hold another format. Declare it as string and convert in queries or a view.

Does Athena have a to_date function?

No. Use date_parse, CAST(... AS date) or date() instead.

How do I add days to a date in Athena?

date_add('day', 7, ts), or ts + INTERVAL '7' DAY.

How do I get the difference between two timestamps in Athena?

date_diff('minute', start_ts, end_ts), with any unit from second to year.

How do I group by hour or week in Athena?

GROUP BY date_trunc('hour', ts) or date_trunc('week', ts).

How do I parse an ISO 8601 timestamp with a Z in Athena?

from_iso8601_timestamp('2026-10-19T14:30:00Z'), which returns a timestamp with time zone.

How do I get yesterday's date in Athena?

current_date - INTERVAL '1' DAY. Remember that current_date is the UTC date.

How do I get the first day of the month in Athena?

date_trunc('month', current_date); last_day_of_month(current_date) gives the last day.

How do I see a file's modified time in Athena?

Select the hidden column "$file_modified_time", with "$path" and "$file_size" for the file name and size. They work on tables, not views.

How do I show days with no data in Athena?

Generate the days with UNNEST(sequence(start, end, INTERVAL '1' DAY)) and LEFT JOIN your daily counts, using coalesce for zeros.

What time zone does Athena use?

UTC. current_timestamp and current_date are UTC; convert with AT TIME ZONE for local time.

How do I convert a timestamp to epoch milliseconds in Athena?

CAST(to_unixtime(ts) * 1000 AS bigint). to_unixtime alone returns seconds with a decimal part.

What is partition projection with dates in Athena?

Table properties describing the date partition pattern, such as projection.dt.type = date and range 2024-01-01,NOW, so new days need no MSCK REPAIR or crawler.

How do I parse a timestamp inside a JSON column in Athena?

from_iso8601_timestamp(json_extract_scalar(payload, '$.created_at')), or from_unixtime on the cast epoch value.

How do I convert CloudTrail eventtime in Athena?

from_iso8601_timestamp(eventtime). Because all values are UTC ISO strings, text filters such as eventtime >= '2026-10-19T00:00:00Z' also work.

How do I parse S3 access log dates in Athena?

parse_datetime(requestdatetime, 'dd/MMM/yyyy:HH:mm:ss Z') for values like 19/Oct/2026:14:30:00 +0000.

What is the timestamp precision in Athena?

Milliseconds, shown as timestamp(3). Iceberg can store microseconds, but Athena reads and writes timestamps to the millisecond.

How do I export Athena dates so Excel recognizes them?

Output date_format(ts, '%Y-%m-%d %H:%i:%s') or date(ts). ISO strings with a T and Z often stay as text in Excel.

What is the Athena equivalent of DATEADD and DATEDIFF?

date_add('day', 7, ts) and date_diff('day', a, b), with the unit as a quoted string first.

What is the Athena equivalent of TO_CHAR?

date_format(ts, '%Y-%m-%d') with MySQL-style codes, or format_datetime(ts, 'yyyy-MM-dd') with Java-style patterns.

Why does one day have 23 hours in my Athena report?

Daylight saving time in your reporting zone. Measure durations in UTC and expect odd totals on change days.

Why are my day totals off at midnight in Athena?

Usually partitions filed by arrival time or UTC grouping. Widen the partition filter by a day and group in your business time zone.

Should I store timestamps as strings in Athena tables?

For text data in non-native formats, yes, then convert in a view; for data you query often, convert once to Parquet with a real timestamp column.

For readers preparing for an AWS certification

We have been covering AWS certifications alongside our fixes, so here is a short extra for readers studying for one. If you only came for the fix, you already have it; feel free to skip this part.

Exams that test this topic: Data Engineer – Associate • Solutions Architect – Associate (SAA-C03) • Developer – Associate (DVA-C03 from December 1, 2026).

Here are the exam-style questions this topic tends to raise, with the reasoning you need to answer them:

1. Which serverless AWS service runs SQL queries directly on data in Amazon S3?

Amazon Athena.

2. How can you reduce the data Athena scans, and so the cost, for date-filtered queries?

Partition the data by date and filter on the partition column; store it in a columnar format such as Parquet.

3. Which Athena statement writes query results to S3 as a new table, for example converting CSV to Parquet?

CREATE TABLE AS SELECT (CTAS).

4. Which service maintains the table definitions that Athena queries?

The AWS Glue Data Catalog.

5. Athena bills for which unit of work on standard queries?

The amount of data scanned per query.

6. A dataset in S3 stores event times as ISO 8601 strings. How should a data engineer make them easy to query?

Keep the column as a string and convert in a view, or rewrite the data to Parquet with a timestamp column.

7. Which format makes Athena queries faster and cheaper than CSV for large datasets?

Apache Parquet (or ORC), columnar and compressed.

Exam codes and dates change often. Our AWS certification changes page keeps track of what is current.

πŸ“š ALSO READ

Where to go next, from flow log queries to Glue jobs:

📌 Bookmark this; the format codes table and the conversion cheat sheet are the parts you will come back for.

Jake's orders-per-day query now runs off a view with one from_iso8601_timestamp in it, filters the dt partition as plain text, and groups by the shop's local day in London time. It scans one week of logs instead of two years, and the numbers match the till. Ethan's tip, pinned above the team's query library: "Strings go in, timestamps come out. Tell Athena the format once."

📌 If you keep one line from this page

Athena reads only yyyy-mm-dd hh:mm:ss directly: convert ISO strings with from_iso8601_timestamp, custom formats with date_parse (%i is minutes), epochs with from_unixtime, and compare with TIMESTAMP literals while filtering partition columns as plain text.

CAST(ts AS date) for dates · date_format for strings · AT TIME ZONE for local time · try() for messy rows.

Revision note. Written October 19, 2026. Most timestamp errors in Athena are a format Athena was never told about; the functions on this page tell it.

Related