-4

I need to change the date format from yyyy-mm-dd to dd/mm/yyyy, but I need to keep the date type for the column. So far, I have used this method that changes the format correctly but transforms the column type from date to text.

TO_CHAR(mydate::TIMESTAMP WITH TIME ZONE, 'dd/mm/yyyy'::TEXT) ;

How can I do it with keeping the date type?

Thanos Markou
  • 2,587
  • 3
  • 25
  • 32
wiltomap
  • 3,933
  • 8
  • 37
  • 54
  • 4
    The whole idea of a date type is that it is stored in a way that's convenient to the database (e.g. for sorting and filtering). You should do your formatting when you output the data. – Glorfindel Jul 20 '15 at 09:01
  • 2
    Datetime values in relational databases are usually stored in binary format and is depicted in the format predetermined by the database. So, If you'd like to depict it differently have to transform it to character value and provide the different format as you do it in your query. – Ispirer SQLWays Migrations Jul 20 '15 at 09:03
  • I understand perfectly... I have an issue with a framework that helps to build queries for a website form (a bit long to explain here...). I just wanted to make sure that such a transformation was possible or not, it would have make it easier for me. – wiltomap Jul 20 '15 at 09:43

1 Answers1

1

This is a misunderstanding.
If your data type is date, then it is stored without format. It's just a 4-byte integer counting seconds since 2000.

You can format it any way when displaying to the client.

SELECT to_char(mydate, 'yyyy-mm-dd') AS one_way
      ,to_char(mydate, 'dd/mm/yyyy') AS other_way

'yyyy-mm-dd' happens to be ISO 8601 format, which is the default text representation in many locales.

You can always create a VIEW with a text representation of the date:

CREATE VIEW v_tbl_with_date_foramt AS
SELECT id, some_column, to_char(mydate, 'dd/mm/yyyy') AS mydate_text
FROM tbl;

But now it's a text column, not a date column.

datestyle is responsible for how input date literals are interpreted.

LC_TIME regulates how date/time functions behave.

The default display is the ISO 8601: 'yyyy-mm-dd'.

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Thanks... but I needed a DATE type column with date displayed as dd/mm/yyyy. So far, the more I can do is displaying either date as yyyy-mm-dd (default) or as dd/mm/yyyy through a TEXT column. – wiltomap Jul 20 '15 at 20:14
  • @wiltomap: The point is that the data type `date` is completely independent of how it's displayed. The default text representation is determined by your locale. Or you can use `to_char()` to get a particular format. The result is a `text` (representing a date), though, not a `date`. – Erwin Brandstetter Jul 20 '15 at 20:25