Skip to Main Content
Forums

MySQL Database

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

MySQL v5.7.18 time stamp and time zone problem

CONCEPT21May 5 2017 — edited May 12 2017

Hello!  It is me again!

I have 2 installations of MySQL v5.7.18.  One is on Oracle Linux 7.3 and the other is on Ubuntu 16.04.

Both of their mysql log records show time stamp in GMT (UTC).  I have checked my system OS's time zone setting and they are set correctly to my local time zone GMT+8.

So how do I make the mysql log records being shown in my local time zone?  In the older version of mysql, their log records were shown in my local time zone.

Hope to hear your helpful opinion. 

This post has been answered by CONCEPT21 on May 9 2017
Jump to Answer

Comments

mathguy

User_<whatever> wrote:

create table ##input1
(Billid int,
Ctextid int,
info JSON,
user varchar(30),
created datetime2
)

Posting CREATE TABLE and INSERT statements is great.
But it is not so great when they have obvious errors, which you could catch easily if you would test your statements yourself, in an Oracle database, before posting them here. (Please don't say you did - that would be a lie.)
USER is a reserved keyword in Oracle; the db will not let you use it as a column name, unless you enclose it in double-quotes. (However, that would be a very poor practice: just don't use keywords as identifiers and you won't get into all sorts of problems later.)
Worse, there is no DATETIME2 data type in Oracle. There is no way you tested your CREATE TABLE on an Oracle database and it didn't throw an error at least for that reason. I don't suppose you created a user-defined data type by that name, did you?
At this point in trying to help you, I stopped. If you can't even test your code before you post it (which would take a few seconds at most), how can I trust that the rest is OK? Even if I fix the data type myself, and I am able to create the table, and I understand your question, how do I know that the way you asked the question is correct? I fear that I may work on your problem, and later you will come back and say "sorry, I misspoke; in fact my problem is...." Do you see how that could be a problem?

User_6TGRX

@mathguy : Please accept my apologies!..I recently moved from using SQL Server to Oracle SQL ..Also I am using a BenchSQL interface ..But that is not a reason..I will figure out the way to test the oracle DDL soon...Meanwhile please find below the changes to my DDL..
Input table

create table ##input1

(Billid int,

Ctextid int,

info IS JSON

cuser varchar(30),

created timestamp

)

insert into ##input1 values

"('2132','91156','[

{

""description"": ""fabula "",

""dCode"": ""8901"",

""CodeId"": ""90001"",

""messages"": [

""""

],

""Number"": 1

},

{

""description"": ""EXper "",

""dCode"": ""9034"",

""CodeId"": ""88343"",

""messages"": [

""""

],

""Number"": 2

}

]','amt1','08/03/2020 17:07'),"

"('2132','91156','[

{

""description"": ""fabula "",

""dCode"": ""8901"",

""CodeId"": ""90001"",

""messages"": [

""""

],

""Number"": 1

},

{

""description"": ""EXper "",

""dCode"": ""9034"",

""CodeId"": ""88343"",

""messages"": [

""""

],

""Number"": 2

},

{

""description"": ""siluka "",

""dCode"": ""887756"",

""CodeId"": ""883773"",

""messages"": [

""""

],

""Number"": 3

}

]','all1','08/03/2020 21:07'),"

"('5678','99344','[

{

""description"": ""TORYA "",

""dCode"": ""99002"",

""CodeId"": ""988332"",

""messages"": [

""""

],

""Number"": 1

},

{

""description"": ""triact "",

""dCode"": ""90774"",

""CodeId"": ""7800034"",

""messages"": [

""""

],

""Number"": 2

}

]','jk1','08/04/2020 18:07'),"

"('5678','99344','[

{

""description"": ""TORYA "",

""dCode"": ""99002"",

""CodeId"": ""988332"",

""messages"": [

""""

],

""Number"": 1

},

{

""description"": ""triact "",

""dCode"": ""90974"",

""CodeId"": ""78034"",

""messages"": [

""""

],

""Number"": 2

}

]','jk2','08/05/2020 18:07'),"

"('7789','60045','[

{

""description"": ""ABNORMAL FINDINGS HELA "",

""dCode"": ""Z003345"",

""CodeId"": ""288897"",

""messages"": [

""""

],

""Number"": 1

},

{

""description"": ""IMPACTED BILATERAL "",

""dCode"": ""U8923"",

""CodeId"": ""7324"",

""messages"": [

""""

],

""Number"": 2

},

{

""description"": ""IMMUNIZATION "",

""dCode"": ""H678"",

""CodeId"": ""26519"",

""messages"": [

""""

],

""Number"": 3

},

{

""description"": "" RUPT EAR DRUM "",

""dCode"": ""I0892"",

""CodeId"": ""567123"",

""messages"": [

""""

],

""Number"": 4

},

{

""description"": ""CHILDHOOD FEVER "",

""dCode"": ""Y98620"",

""CodeId"": ""55467"",

""messages"": [

""""

],

""Number"": 5

}

]','ec1','08/07/2020 6:07'),"

"('7789','60045','[

{

""description"": ""ABNORMAL FINDINGS HELA "",

""dCode"": ""Z8897645"",

""CodeId"": ""288897"",

""messages"": [

""""

],

""Number"": 1

}, {

""description"": ""IMMUNIZATION "",

""dCode"": ""H67891"",

""CodeId"": ""26519"",

""messages"": [

""""

],

""Number"": 2

},

{

""description"": "" RUPT EAR DRUM "",

""dCode"": ""I0892"",

""CodeId"": ""567123"",

""messages"": [

""""

],

""Number"": 3

},

{

""description"": ""Kirolo substaniss FEVER "",

""dCode"": ""J18907"",

""CodeId"": ""66712"",

""messages"": [

""""

],

""Number"": 4

}','ec2','08/07/2020 17:07')

Output table

create table ##output1

(billid int,

ctextid int,

codername varchar(30),

correctedcode varchar(100),

deletedcode varchar(100),

addedcode varchar(100)

)

insert into ##output1 values

('2132','91156','amt1','','','887756'),

('5678','99344','jk1','90774','',''),

('7789','60045','ec1','Z003345,H678','U8923,Y98620','J18907')

1 - 2

Post Details

Added on May 5 2017
#mysql-faqs, #mysql-user-groups
5 comments
190 views