Not the answer you need?
Register and ask your own question!

mysqldump or export to csv in a different timestamp format

RkotRkot Current User Role Contributor

Hi

I have a Percona Xtradb cluster 5.7 with many tables with timestamp column/columns. I need to export the data of all the tables into a csv or mysqldump file . The problem i am facing is that the output file needs to be in a different timestamp format as below

2020-01-22 19:24:27.953  (current)-----------------> 22-JAN-2020 7:24:27.953 PM ( desired)

Is there any way i can set any variable at session level to take a dump of the data ?

The problem with using DATE_FORMAT is that, i need to set it for each timestamp column of the table . Also I will need to define the columns explicitly instead of using 'SELECT *'

Answers

  • yves.trudeauyves.trudeau Percona Percona Staff Role

    What about something like this:

    select concat('SELECT ',group_concat(COLUMN_NAME order by ORDINAL_POSITION),' FROM ',TABLE_NAME) as statement

    from (select ORDINAL_POSITION, table_schema, table_name, if(DATA_TYPE='timestamp',concat('DATE_FORMAT(CONVERT_TZ(',COLUMN_NAME,',"GMT","EST"),"%e-%b-...")'),COLUMN_NAME) as COLUMN_NAME from COLUMNS order by table_schema, table_name, ORDINAL_POSITION) st

    where TABLE_SCHEMA='sysbench' and TABLE_NAME = 'sbtest%' group by table_name;

    +------------------------------------------------------------------------------------+

    | statement                                                                         |

    +------------------------------------------------------------------------------------+

    | SELECT id,k,c,pad,DATE_FORMAT(CONVERT_TZ(ts,"GMT","EST"),"%e-%b-...") FROM sbtest1 |

    +------------------------------------------------------------------------------------+

    Adjust the date format and the final where clause of course. I added a timestamp column to the sbtest1 table to illustrate.

  • RkotRkot Current User Role Contributor

    Thank you so much, it worked

  • RkotRkot Current User Role Contributor

    I need to add the format for DATE datatype too , when i tried to use ELSEIF for the above quey , it gives me syntax error. Can you please suggest how to incorporate the date datatype also ? I tried as below -


    if(DATA_TYPE='timestamp' then concat('DATE_FORMAT(',COLUMN_NAME,',"%d-%b-%y %l.%i.%s %p")'),COLUMN_NAME) as COLUMN_NAME

    elseif(DATA_TYPE='date' then concat('DATE_FORMAT(',COLUMN_NAME,',"%d-%b-%y")'),COLUMN_NAME) as COLUMN_NAME) as COLUMN_NAME

    end if

  • yves.trudeauyves.trudeau Percona Percona Staff Role

    Hi,

    personally I would merge the IF statements together:

    if(DATA_TYPE='timestamp' then concat('DATE_FORMAT(',COLUMN_NAME,',"%d-%b-%y %l.%i.%s %p")'), if(DATA_TYPE='date' then concat('DATE_FORMAT(',COLUMN_NAME,',"%d-%b-%y")'),COLUMN_NAME)) as COLUMN_NAME

    I hope I have the parenthese right, the above is untested.

Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.