0

The following will return the day in Italian:

SET lc_time_names = 'it_IT';
select date_format('2018/01/01','%W') as day_italian;

However I need to convert or format the date into multiple languages, so it would return me another column in English, Japanese, so on...

My problem is that I have to set the locale BEFORE running the select command.

2
  • 1
    The only way to solve this task I see is to create a table which keep matches between term in std (en-US, for example) locale, another locale and this term in another locale. Joining this table to a query source and replacing terms in a date formatted in std locale to a locale you specify will give desired result. I.e. create your own locale-convertion (maybe in a function form). – Akina Sep 12 '18 at 4:36
  • sounds reasonable @Akina – danblack Sep 12 '18 at 5:49
0

With a Stored Function, you can get it all in a single SELECT.

DELIMITER //

CREATE FUNCTION WDay(_d DATE, _locale VARCHAR(5))
    RETURNS VARCHAR(22)
    DETERMINISTIC
    SQL SECURITY DEFINER
BEGIN
    SET @@lc_time_names = _locale;
    RETURN DATE_FORMAT(_d, '%W');
END;
//
DELIMITER ;

SELECT WDay(NOW(), 'it_IT') AS it,
       WDay(NOW(), 'es_ES') AS es,
       WDay(NOW(), 'en_US') AS us;

+----------+--------+----------+
| it       | es     | us       |
+----------+--------+----------+
| giovedì  | jueves | Thursday |
+----------+--------+----------+

Caveat: This has the side effect of changing @@lc_time_names.

2
  • I love it! Maybe in the last line I can set the lc_time_names to my own "default language" – Calvin Oct 4 '18 at 23:16
  • @Calvin - sounds like a plan. – Rick James Oct 5 '18 at 2:50
0

Sorry, I've looked into the source code around lc_time_names and there is no solution around getting multiple lc_time_name translations with a single query.

MariaDB has an information_schema table called locales however it doesn't got as far as exporting the lc_time_names of days/months etc. All the names are stored in the server (code, not a database), and it would be moderately easy to export these to an information_schema table, however noone has written the code to do this.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.