<p>Either <code>request_timestamp</code> needs to be converted from the ISO 8601 formatted string into a timestamp with time zone, or <code>current_timestamp</code> needs to be converted into the string.</p>

<p>One method is to convert <code>request_timestamp</code> into a string in format <code>%Y-%m-%d</code>, e.g.</p>

<ul>
<li><code>split_part(request_timestamp, 'T', 1)</code></li>
<li><code>substr(request_timestamp, 1, 10)</code></li>
</ul>

<p>then compare it with <code>current_timestamp</code> which can be converted to the ISO 8601 formatted string.</p>

<p>Here is the final query:</p>

<pre><code>SELECT *
FROM elb_logs
WHERE split_part(request_timestamp, 'T', 1) = split_part(to_iso8601(current_timestamp), 'T', 1)
LIMIT 10
</code></pre>

CC BY-SA 4.0
2019-01-31T16:28:48.547Z
2019-01-31T16:28:48.547Z
kenorb
<p>I've tried this query:</p>

<pre><code>SELECT request_timestamp
FROM elb_logs
WHERE date_trunc('day', request_timestamp) = date_trunc('day', current_timestamp)
LIMIT 10
</code></pre>

<p>but it fails with:</p>

<blockquote>
  <p>SYNTAX_ERROR: line 3:7: Unexpected parameters (varchar(3), varchar) for function date_trunc. Expected: date_trunc(varchar(x), date) , date_trunc(varchar(x), time) , date_trunc(varchar(x), time with time zone) , date_trunc(varchar(x), timestamp) , date_trunc(varchar(x), timestamp with time zone)</p>
</blockquote>

<p>I've checked the <a href="https://prestodb.github.io/docs/0.172/functions/datetime.html" rel="nofollow noreferrer">Date and Time Functions and Operators</a> docs page, but I can't see anything wrong.</p>

<p>I've created the <code>elb_logs</code> table in Amazon Athena according to <a href="https://docs.aws.amazon.com/athena/latest/ug/elasticloadbalancer-classic-logs.html#create-elb-table" rel="nofollow noreferrer">this page</a>.</p>

<p>What I am missing?</p>

<amazon-web-services><sql>
How to filter ELB logs by today's date?
kenorb
# How to filter ELB logs by today's date?

(Asked by: username_0 on 2019-01-31)

I've tried this query:
SELECT request_timestamp
FROM elb_logs
WHERE date_trunc('day', request_timestamp) = date_trunc('day', current_timestamp)
LIMIT 10

but it fails with:

SYNTAX_ERROR: line 3:7: Unexpected parameters (varchar(3), varchar) for function date_trunc. Expected: date_trunc(varchar(x), date) , date_trunc(varchar(x), time) , date_trunc(varchar(x), time with time zone) , date_trunc(varchar(x), timestamp) , date_trunc(varchar(x), timestamp with time zone)

I've checked the Date and Time Functions and Operators docs page, but I can't see anything wrong.
I've created the elb_logs table in Amazon Athena according to this page.
What I am missing?

**Tags:** amazon-web-services, sql


---

## 1 answers

### Answer by username_0 on 2019-01-31. Score: 1 | Accepted answer

Either request_timestamp needs to be converted from the ISO 8601 formatted string into a timestamp with time zone, or current_timestamp needs to be converted into the string.
One method is to convert request_timestamp into a string in format %Y-%m-%d, e.g.

split_part(request_timestamp, 'T', 1)
substr(request_timestamp, 1, 10)

then compare it with current_timestamp which can be converted to the ISO 8601 formatted string.
Here is the final query:
SELECT *
FROM elb_logs
WHERE split_part(request_timestamp, 'T', 1) = split_part(to_iso8601(current_timestamp), 'T', 1)
LIMIT 10


---

