CODEWITHSUNDEEP
dateamazon-redshift
user2828360
user2828360

Reputation: 87

Unable to truncate date date from timestamp

In my redshift table, I have a column that has timestamp stored as - '2019-09-03 00:00:00'. I want to extract just the date out of it.

Tried both to_date and trunc function

select col, min(date) from table group by col

Also tried 

select col, trunc(min(date)) from table group by col

I am still getting proper timestamp and not just date part.

Upvotes: 0

Views: 486

Answers (1)

botchniaque
botchniaque

Reputation: 5084

In redshift truncating timestamp will always give you timestamp back, but it's not a big deal - you can still store it efficiently, filter using it, group by it etc. If you really want to store just the date then cast your timestamp object to date. 

select '2019-09-03 01:13:11' str, str::timestamp as ts, date_trunc('day', ts), str::date as d

str                 ts                          date_trunc                  d
2019-09-03 01:13:11 2019-09-03 01:13:11.000000  2019-09-03 00:00:00.000000  2019-09-03

Upvotes: 1

Related Questions