Postgres timestamp to date

0
votes

I am building a map in CartoDB which uses Postgres. I'm simply trying to display my dates as: 10-16-2014 but, haven't been able to because Postgres includes an unneeded timestamp in every date column.

Should I alter the column to remove the timestamp or, is it simply a matter of a (correct) SELECT query? I can SELECT records from a date range no problem with: 

SELECT * FROM mytable 
WHERE myTableDate >= '2014-01-01' AND myTableDate < '2014-12-31'

However, my dates appear in my CartoDB maps as: 2014-10-16T00:00:00Z and I'm just trying to get the popups on my maps to read: 10-16-2014.

Any help would be appreciated - Thank you!

2
postgresqlcartodb
Dates in CartoDB are in fact TIMESTAMP WITH TIME ZONE type so as @Craig suggested you need to use to_char function to format your date in the form works better for you to be displayed on infowindows or labels. - Jorge Sanz

2 Answers

2
votes

You are confusing storage with display.

Store a timestamp or date, depending on whethether you need time or not.

If you want formatted output, ask the database for formatted output with to_char, e.g.

SELECT col1, col2, to_char(col3, 'DD-MM-YY'), ... FROM ...;

See the PostgreSQL manual.

There is no way to set a user-specified date output format. Dates are always output in ISO format. If PostgreSQL let you specify other formats without changing the SQL query text it'd really confuse client drivers and applications that expect the date format the protocol specifies and get something entirely different.

0
votes

You have two basic options.

1 Change the column from a timestamp to a date column. 2 Cast to date in your SQL query (i.e. mytimestamp::date works).

In general if this is a presentation issue, I don't usually think that is a good reason to muck around with the database structure. That's better handled by client-side processing or casting in an SQL query. On the other hand if the issue is a semantic one, then you may want to revisit your database structure.