DATE format GTM in template

[LinkView_Maze]

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

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

[GogoVega]

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

[LinkView_Maze]

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

I changed it like that but it doesn't work

[E1cid]

@LinkView_Maze

But the date in my database is on format YYYY-MM-DD,

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

[LinkView_Maze]

this is my request mysql

SELECT * FROM authorized ORDER BY name ASC, lastname ASC

[E1cid]

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

[GogoVega]

Try with

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

[LinkView_Maze]

no i have no entry in my table with this format

[LinkView_Maze]

this is my database

[Colin]

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

[E1cid]

Try

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

Or similar

[LinkView_Maze]

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;

[UnborN]

@LinkView_Maze

in format YYYY-MM-DD not GTM

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