1

i have a table with below 5 columns..timestamp is data type for timedate related column :

 Region     event_time     start_time     mid_time   end_time
 India   2013-11-03 13:00:00        
 America
 Holland
Switzerland

I have source from where data for above column is coming at a particular interval..now while insertion i need to add time .

For ex. if in csv file i have event_time of 2013-11-03 13:00:00 , then for India time gets added by 5:30 ( due to GMT ) before insertion..and final inserted values should be 2013-11-03 18:30:00..same applies for rest of Regions and datetime columns..how should i do this...Also daylight saving will include for other Regions that do have this

I tried adddate , addtime functions..but this doesn't work...i tried updating after insertion , but this is not a solution i want.....any help ??

simplifiedDB
  • 669
  • 6
  • 17
  • 36
  • Show your insert statement. – Mihai Dec 03 '13 at 13:10
  • 1 . Im using load infile statement to put csv file into table T1. 2. then from T1 m moving data from another table using: INSERT INTO T2 SELECT * FROM T1 ON DUPLICATE KEY UPDATE T2.event_time=T1.event_time,T2.start_time=T1.start_time........; – simplifiedDB Dec 04 '13 at 05:53

1 Answers1

0

I doubt what are you trying can be done in a single go.You could do it specifying timezone for all inserts for a particular country. Or something like this after insert:

UPDATE....
  CASE WHEN Region='India' THEN event_time = event_time+INTERVAL 330 MINUTE 
  CASE WHEN Region='America' THEN event_time = event_time+INTERVAL someother MINUTE
  CASE...
Mihai
  • 1,543
  • 1
  • 13
  • 16