postgres sql, date_trunc without extra zeroes

Question

This is an excerpt from my sql query

SELECT
date_trunc(
    'day',
    to_timestamp(requests.date_created)
)AS DAY,

this is my output 2013-02-04 00:00:00+00

I want this to be just 2013-02-04

how do I get the desired result?

The trouble is probably from using a timestamp instead of a date. Try using `to_date`. What type of data does the `day` attribute hold that is making this function necessary? — cms_mgr, Feb 04 '13 at 16:44

score 29 · Accepted Answer · answered Feb 04 '13 at 16:43

29

please, try

SELECT date_trunc(...)::date;

answered Feb 04 '13 at 16:43

Pavel Stehule

this worked for me, I'll accept the answer in a few minutes when it lets me – CQM Feb 04 '13 at 16:45
To anyone wondering, the :: operator is type casting (see http://stackoverflow.com/a/5758523/2087198) – Bruno Peres Jun 09 '16 at 15:20

score 4 · Answer 2 · answered Feb 04 '13 at 16:45

4

You can use to_date to specify your date format

TO_DATE(date_col, 'yyyy-mm-dd')

answered Feb 04 '13 at 16:45

Matheus

I think this works in Redshift, but it doesn't work in vanilla Postgres. – szeitlin Oct 29 '18 at 22:56

2 Answers2