0

I have two tables and they both have a Time field. I am trying to get data from those two tables and when I run the query, the results are fine(what I am looking for).

Start_time            Break_time                Finish_time
2016-01-01 01:00:00   2016-01-01 01:05:51       2016-01-01 01:05:59 
2016-01-01 01:00:00   2016-01-01 01:05:51       2016-01-01 01:06:12 
2016-01-01 01:00:00   2016-01-01 01:06:26       2016-01-01 01:06:26 
2016-01-01 01:00:00   2016-01-01 01:06:21       2016-01-01 01:06:35 
2016-01-01 01:00:00   2016-01-01 01:06:49       2016-01-01 01:06:59 
2016-01-01 01:00:00   2016-01-01 01:06:51       2016-01-01 01:07:24 
2016-04-14 12:03:00   2016-04-14 12:27:39       2016-04-14 12:28:04 
2016-04-14 12:03:00   2016-04-14 12:27:28       2016-04-14 12:28:07
2016-04-14 12:03:00   2016-04-14 12:27:38       2016-04-14 12:28:07 
2016-04-14 11:30:00   2016-04-14 12:25:22       2016-04-14 12:28:08 
2016-04-14 12:03:00   2016-04-14 12:27:57       2016-04-14 12:28:08

However, when I try to create a table so for the results to go in, the Time completely change.

Start_time            Break_time                Finish_time
2016-01-01 01:00:00   2016-01-01 01:05:51       2016-01-01 01:05:59 01  
2016-01-01 01:00:00   2016-01-01 01:05:51       2016-01-01 01:06:12 01  
2016-01-01 01:00:00   2016-01-01 01:06:26       2016-01-01 01:06:26 01  
2016-01-01 01:00:00   2016-01-01 01:06:21       2016-01-01 01:06:35 01  
2016-01-01 01:00:00   2016-01-01 01:06:49       2016-01-01 01:06:59 01  
2016-01-01 01:00:00   2016-01-01 01:06:51       2016-01-01 01:07:24 01  
2016-01-01 01:00:00   2016-01-01 01:07:14       2016-01-01 01:07:27 01  
2016-01-01 01:00:00   2016-01-01 01:06:26       2016-01-01 01:07:32 01  
2016-01-01 01:00:00   2016-01-01 01:07:03       2016-01-01 01:07:49 01  
2016-01-01 01:00:00   2016-01-01 01:07:51       2016-01-01 01:08:01 01  
2016-01-01 01:00:00   2016-01-01 01:07:44       2016-01-01 01:08:07 01  
2016-01-01 01:00:00   2016-01-01 01:07:47       2016-01-01 01:08:08 01  
2016-01-01 01:00:00   2016-01-01 01:07:06       2016-01-01 01:08:31 01  
2016-01-01 01:00:00   2016-01-01 01:08:25       2016-01-01 01:08:53 01

The Start_time in the first table is TIMESTAMP(Type), CURRENT_TIMESTAMP(DEFAULT), on update CURRENT_TIMESTAMP(Extra). In the second table, the Break_time and Finish_time are both VARCHAR(TYpe), NULL(Default), None(Extra). I think this is what is causing the issue but can't figure out how to fix it. Any thoughts please?

Drake
  • 53
  • 9

1 Answers1

0

MySQL converts your inserted time to UTC depending on current timezone. Also it converts back to current one on select. Here a similar question.

How do I get the current time zone of MySQL?

But you have to use DATETIME field for Break_time and Finish_time. On insert MySQL doesn't convert them because of their type.

Community
  • 1
  • 1
tanaydin
  • 5,171
  • 28
  • 45
  • thanks for the above, it makes sense. HowEver, when I try to convert those to fields to DATETIME, I keep getting the error message "Incorrect datetime value: '' for function str_to_date". – Drake Oct 28 '16 at 10:51
  • UPDATE 2016_Data SET startt_time = STR_TO_DATE(start_time,'%Y-%m-%d %H:%i:%s') – Drake Oct 28 '16 at 10:53
  • Isn't start_time already date? It seems you should use this function on other fields that defined as varchar. It will be better to add a datetime field like Break_time_tmp and update this fields value to DATETIME value instead of string. When all rows are updated, delete Break_time and rename Break_time_tmp to Break_time – tanaydin Oct 28 '16 at 13:08