Skip to main content
May 2, 2024
Question

MariaDB : DateTime format doesnot have timezone while featching the data using VIEW

  • May 2, 2024
  • 2 replies
  • 0 views

I have a scenario where I use exportEntityToExcel smart service to export a VIEW data (script below) in an excel file. There is one filed which has datatime (CREATED_ON) in it. I want to display/export to excel the timezone along with datetime in this field.

Existing Format: 11/27/23 02:43 

Expected Format: 11/27/23 02:43 AM/PM PST/CST/MST/EST 

.

CREATE VIEW `DUMMY_VW`  AS 
SELECT 
`b`.`CITY` AS `CITY`, 
`i`.`CREATED_ON` AS `CREATED_ON`, 
`i`.`CREATED_MONTH` AS `CREATED_MONTH`, 
`i`.`CREATED_YEAR` AS `CREATED_YEAR`, 
 FROM (`DUMMY_STATUS` `i` left join `DUMMY_PROFILE` `b` on(`i`.`ID` = `b`.`D_ID`)) ;

    2 replies

    daniell
    May 2, 2024

    exportDataStoreEntityToExcel is limited in the formatting you can apply to the output data, but you could try converting your CREATED_ON field from an actual datetime to a formatted text string in your view and then exporting that

    stefanhelzle0001
    Brainy
    May 3, 2024

    In the database, dates are stored in UTC and does not know about time zones. And the export function only takes what it gets.