CODEWITHSUNDEEP
postgresql
jotamon
jotamon

Reputation: 1614

Create new column with reformated date

I have a column named 'date' formatted as date-type with values like 2007-08-24T00:00:00Z

I would like to populate a new column called date2 with a reformatted string like August 24th, 2007

Below is my attempt at this. What am I missing?

UPDATE myTable SET date2 = date_part('Mon DD, YYYY',date);

Upvotes: 0

Views: 49

Answers (3)

Str.
Str.

Reputation: 1439

If you are for some reason fed up with calling the date formatting functions, you can put your extract() or to_char() into a function, or create an appropriate view on your table.

Upvotes: 0

IMSoP
IMSoP

Reputation: 97818

As noted in the comments, if your column is one of the built-in date/time types - date, timestamp, etc - then it is not stored in the database with any particular string format, but as abstract data. This is the only way you should ever store dates in the DB, as it allows you to perform a whole range of operations directly on the data.

To format such a value, you apply an appropriate formatting when you select the data. I would strongly recommend against storing this formatted string anywhere - it adds no value to your database, since any format can be calculated at any time, but does mean you have duplicated data to manage in future.

The formatting functions take a slightly unusual specifier format; in your case, I believe you want 'FMMonth Dth, YYYY':

 SELECT
    to_char("date", 'FMMonth Dth, YYYY') as formatted_date
 FROM
    my_table;

As an alternative trick, if you happen to be using PHP, you can select the date out as a Unix timestamp, and then use PHP functions to format that:

 SELECT
    extract(epoch from "date") as unix_date
 FROM
    my_table;

Upvotes: 1

jotamon
jotamon

Reputation: 1614

this does the trick

UPDATE myTable SET date2 = to_char(date,'Month DD, YYYY')

Upvotes: 0

Related Questions