Canvas: duplicate date across table using formatdate

[TsuWeiQuan]

Hello,

I am using canvas and have some trouble getting a nice formatted time.

I would like to ask why is my formatted time on the right most column always output UTC time and it duplicates across the table.
I followed this blog and it says that
| mapColumn "formatted time" fn={ date | formatdate format="hh:mm A" }
should parse the time nicely.

I also realised that formatted time is actually showing my local time in UTC instead of the time column time.

This is currently my test query:

filters
| essql 
  query="SELECT CAST(\"@timestamp\" AS DATETIME ) as time, system.memory.actual.used.bytes AS Used, system.memory.actual.free AS Free FROM \"metricbeat-*\" WHERE host.hostname='NUSSERVER' AND system.memory.actual.used.bytes IS NOT NULL AND system.memory.used.bytes IS NOT NULL ORDER BY \"@timestamp\" DESC" tz="UTC+8"
| mapColumn "formattedtime" fn={date | formatdate format="hh:mm A"}
| table 
  font={font family="'Open Sans', Helvetica, Arial, sans-serif" size=14 align="left" color="#000000" weight="normal" underline=false italic=false}
| render containerStyle={containerStyle}

I have tried:
| mapColumn "formattedtime" fn={getCell column="time" | formatdate format="hh:mm A"} but it returned invalid date.

How can i make my formatted time to be the same as the time on the time (1st) column with a different format?

[TsuWeiQuan]

I have found my error.
My SQL query has set the timestamp as DATETIME format.
So i have to naturally convert the type from DATETIME to DATE format.

| alterColumn column=time type=date
| mapColumn "formattedtime" fn={getCell column=time | formatdate format="YYYY-MM-DD hh:mm:ss" }

[tims (Tim Schnell)]

Looks like you figured it out @TsuWeiQuan, let us know if you have other questions!