Menu
I have a table with a column month(integer). In this column I store values like 1, 2, .. 12.
But I have to show the month name.
Ex: If the value is 1 I need to display jan.
633
In PostgreSQL, if you already have a month name, but you want to convert that name to the month number, you can do this with the EXTRACT() function.
How do I sort by month name in PostgreSQL? The TO_DATE(birthday_month, 'Month') function converts a full month name to a date in the ' 0001-MM-01 ' format. For example, you get ' 0001-12-01 ' for December. You can now use the EXTRACT(MONTH FROM date) function to extract the month from this date value.
CAST ( expression AS target_type ); where: The expression can be a constant or a table column or any expression that finally resolves to a value, The target_type is the final datatype to which you want to convert the above expression to.
The TO_DATE function in PostgreSQL is used to converting strings into dates. Its syntax is TO_DATE(text, text) and the return type is date. The TO_TIMESTAMP function converts string data into timestamps with timezone. Its syntax is to_timestamp(text, text) .
Basically what @small_duck already posted, but a couple of improvements:
SELECT to_char(to_timestamp (4::text, 'MM'), 'TMmon') A plain cast to text 4::text is enough, no need for to_char(..).
Question asks for lower case "jan", there is a template pattern for that: mon.
If you want to localize the output, prefix the template with the modifier TM.
190
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
