topic Re: timestamp date filter does not work in Data Engineering

timestamp date filter does not work

collierd — Wed, 27 Aug 2025 10:33:03 GMT

Hello

I have a column called LastUpdated defined as timestamp

If I select from the table it displays as (e.g.) 2025-08-27T10:50:31.610+00:00

How do I filter on this without having to be specific with the year, month, day, ...

This does not work:

select * from available where LastUpdated = '2025-08-27T10:50:31.610+00:00'

Feel like there's a better approach than

select * from available where year(LastUpdated) = 2025 and month(LastUpdated) = 8 and day(LastUpdated) = 27 etc.

Thanks

Re: timestamp date filter does not work

WiliamRosa — Wed, 27 Aug 2025 11:40:21 GMT

Hi @collierd, Try this:

WHERE CAST(LastUpdated AS DATE) = DATE '2025-08-27';

Re: timestamp date filter does not work

szymon_dybczak — Wed, 27 Aug 2025 12:08:47 GMT

Hi @collierd ,

You have many options here. You can use

1. Date Truncation - which returns timestamp truncated to the unit (you can use day, quarter, month etc)

%sql SELECT date_trunc('day', LastUpdated), LastUpdated FROM ( SELECT current_timestamp() as LastUpdated ) src WHERE date_trunc('day', LastUpdated) = '2025-08-27'

2. You can use date casting

SELECT CAST(LastUpdated AS DATE) As lastUpdatedCasted, LastUpdated FROM ( SELECT current_timestamp() as LastUpdated ) src WHERE CAST(LastUpdated AS DATE) = '2025-08-27'

3. Range filtering

SELECT LastUpdated FROM ( SELECT current_timestamp() as LastUpdated ) src WHERE LastUpdated >= '2025-08-27' AND LastUpdated < '2025-08-28'

4. Format Matching

%sql SELECT date_format(LastUpdated, 'yyyy-MM'), LastUpdated FROM ( SELECT current_timestamp() as LastUpdated ) src WHERE date_format(LastUpdated, 'yyyy-MM') = '2025-08'

Re: timestamp date filter does not work

Pilsner — Wed, 27 Aug 2025 12:10:28 GMT

Hello @collierd ,

The way I would tackle this would involve data time specifiers. Because your value is likely stored as a timestamp which you can see via the catalog explorer, you cannot compare it to a string value such as "2025-08-27T10:50:31.610+00:00".

To get around this you can use date time specifiers to convert the string into a timestamp using the below code:

to_timestamp('2025-08-27T10:50:31.610+00:00', "yyyy-MM-dd'T'HH:mm:ss.SSSXXX")

If your curious about these date time specifiers there is a fill list of them available here:
https://docs.databricks.com/aws/en/sql/language-manual/sql-ref-datetime-pattern

The basic idea is that you select the relevant specifier (i.e "y" for year) then repeat this character to match the length of the string (so for a 4 digit year you get "yyyy").

I hope this helps but feel free to ask any further questions.

Regards - Pilsner

Re: timestamp date filter does not work

collierd — Wed, 27 Aug 2025 14:05:37 GMT

Thanks

That's useful but I also need the time

Re: timestamp date filter does not work

collierd — Wed, 27 Aug 2025 14:06:42 GMT

Thanks

date_trunc is the moset useful here as it includes the time

date_format only references the date part - although that is useful

Re: timestamp date filter does not work

collierd — Wed, 27 Aug 2025 14:17:15 GMT

Thanks

to_timestamp works well

Re: timestamp date filter does not work

Pilsner — Wed, 27 Aug 2025 14:49:12 GMT

Glad I could help @collierd!

@szymon_dybczak I wasn't aware of the date_trunc function. I like the fact that you can pass simple terms, such as "day" in as a parameter, it makes it easy to read. Thanks!