DATE format GTM in template

Hello

I have a dashboard with template node

But the date in my database is on format YYYY-MM-DD, after select on Mysql the value is in format
Capture d'Ă©cran 2023-12-28 142009

What is the solution for any value il in my template in format YYYY-MM-DD not GTM

Since you are French-speaking, don't you want this kind of format?
"20/12/2012 04:00:00"

Take a look here, I don't know if you can use it directly in the template or if you have to modify the message before

    <td><center>{{msg.payload[$index].date.toLocaleString('fr-FR', { timeZone: 'UTC' })}}</center></td>

I changed it like that but it doesn't work

I would think you can adjust your sql statement to get the correct format.

this is my request mysql

SELECT * FROM authorized ORDER BY name ASC, lastname ASC

Would need to know column types, to make a reply that would work. But try naming the columns instead of *, and also you can use sql functions on the columns then. Dont for get to use AS name function to make the returned object properties more readable.

e.g

SELECT DATE_FORMAT(date_column, "%Y") AS date....

https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-format

Try with

{{new Date(msg.payload[$index].date).toLocaleString('fr-FR', { timeZone: 'UTC' })}}
1 Like

no i have no entry in my table with this format

this is my database

Modify the format in msg.payload before you pass it to the template node.

Try

SELECT 
  ID,
  name,
  lastname,
  plate,
  DATE_FORMAT(date, "%Y-%m-%d") AS date 
FROM 
  authorized 
ORDER BY 
  name ASC, 
  lastname ASC;

Or similar

this is good but the code is

msg.topic = "SELECT ID ,name, lastname,plate, DATE_FORMAT(date, '%Y-%m-%d') AS date FROM authorized ORDER BY name ASC, lastname ASC"; return msg;

If you want the dates to show in your local time,
you'll have to use an additional CONVERT_TZ mysql date function
in order to first convert it to your timezone and then format it ..
otherwise the dates would sometimes show incorrectly.

so adding to @E1cid 's solution

SELECT ID, name, lastname, plate, DATE_FORMAT(CONVERT_TZ(date,'+00:00','+10:00'), '%Y-%m-%d') AS date FROM authorized ORDER BY name ASC, lastname ASC
  • +10:00 as an example. adjust to your local tz

CONVERT_TZ() function

1 Like

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.