CODEWITHSUNDEEP
postgresql
user16693302
user16693302

Reputation:

Year format from yyyy to yyyy-yyyy in postgresql

How do I update all values of a column from yyyy to yyyy-yyyy+1 format in postgresql.

Ex: 1942 to 1942-1943

Upvotes: 0

Views: 53

Answers (2)

rcanpahali
rcanpahali

Reputation: 2643

I recommend you to change your column data type to Date (date_field1) and create another Date column (date_field2) for this operation,

TO_DATE(date_field1, YYYY);

And then you can update your column as shown below,

UPDATE table SET date_field2 = date_field1 + interval '1 year';

Here is date column manipulation methods, hope it helps.

Upvotes: 1

Sebastiaan van den Broek
Sebastiaan van den Broek

Reputation: 6331

I'd say that this is in general not a good idea, as it doesn't really add any value to the data. It appears you are saving these years in a text format which is already a bit of an issue but it could suit your purposes. Although it won't always sort the years the way you'd expect it to.

It's more reasonable to add this extra data when you're actually serializing the data to your users, at the place it is needed.

Upvotes: 1

Related Questions