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

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.

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

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:

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):

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';

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.

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".

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'.

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."

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.

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

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."

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.