0

I am having an update query in my java application :

update STAGE_TBL SET LAST_UPDATE_DTTM = CURRENT_TIMESTAMP where ID = 8

I am getting the LAST_UPDATE_DTTM as 2020-07-08-04.15.37 +0530 CST but I need it in 2020-10-08 08:06:53.812136 (YYYY-MM-DD HH:MM:SS:ss:Ss) EST format .

How to do that ?

Hakan Dilek
  • 2,178
  • 2
  • 23
  • 35
GKr297
  • 185
  • 2
  • 16
  • 4
    Check that `LAST_UPDATE_DTTM` is defined as `TIMESTAMP(6)` in the table structure (not single `TIMESTAMP`). Use `CURRENT_TIMESTAMP(6)` instead of single `CURRENT_TIMESTAMP`. – Akina Jul 09 '20 at 07:17

1 Answers1

1

Have you tried using something like CURRENT_TIMESTAMP(6)? This should give you the timestamp in the format you want. You can find more information about the CURRENT_TIMESTAMP function in this blog post or in the MYSQL DOCs.

To convert the timestamp to EST you could use the following:

CONVERT_TZ( UTC_TIMESTAMP, '+0:00', '-4:00');

Alternatively:

CONVERT_TZ( UTC_TIMESTAMP, 'UTC', 'EST');

In place of CURRENT_TIMESTAMP. For more information about the MYSQL CONVERT_TZ Function go here.

Here you use MYSQLs UTC timestamp to get a timestamp that is 4 hours before UTC time.

For the second methode to work you need to load a timezone table into mysql:

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql

You can find more information on how to load timezone tables into MYSQL on different operating systems in this answer.

Joschua Fink
  • 26
  • 2