Posted By

emregulcan on 07/23/10


Tagged

sql timezone


Versions (?)

Who likes this?

4 people have marked this snippet as a favorite

emregulcan
seloh77
g8rpal
The14thGOD


Timezone database


 / Published in: SQL
 

URL: http://blog.uptimerobot.com/free-timezones-database-for-your-web-apps/

You can run this sql scripts in your database. Firstly it creates a database and after insert records (timezones)

  1. SET FOREIGN_KEY_CHECKS=0;
  2. -- ----------------------------
  3. -- Table structure for timezones
  4. -- ----------------------------
  5. CREATE TABLE `timezones` (
  6. `timezoneID` int(11) NOT NULL AUTO_INCREMENT,
  7. `timezoneName` varchar(50) NOT NULL,
  8. `timezoneValue` time DEFAULT NULL,
  9. `timezoneMinusPlus` varchar(1) DEFAULT NULL,
  10. `timezoneMinuteValue` int(11) DEFAULT NULL,
  11. PRIMARY KEY (`timezoneID`)
  12. ) ENGINE=InnoDB AUTO_INCREMENT=34 DEFAULT CHARSET=utf8;
  13.  
  14.  
  15. INSERT INTO `timezones` VALUES ('1', 'Eniwetok, Kwajalein', '12:00:00', '-', '720');
  16. INSERT INTO `timezones` VALUES ('2', 'Midway Island, Samoa', '11:00:00', '-', '660');
  17. INSERT INTO `timezones` VALUES ('3', 'Hawaii', '10:00:00', '-', '600');
  18. INSERT INTO `timezones` VALUES ('4', 'Alaska', '09:00:00', '-', '540');
  19. INSERT INTO `timezones` VALUES ('5', 'Pacific Time (US & Canada)', '08:00:00', '-', '480');
  20. INSERT INTO `timezones` VALUES ('6', 'Mountain Time (US & Canada)', '07:00:00', '-', '420');
  21. INSERT INTO `timezones` VALUES ('7', 'Central Time (US & Canada), Mexico City', '06:00:00', '-', '360');
  22. INSERT INTO `timezones` VALUES ('8', 'Eastern Time (US & Canada), Bogota, Lima', '05:00:00', '-', '300');
  23. INSERT INTO `timezones` VALUES ('9', 'Atlantic Time (Canada), Caracas, La Paz', '04:00:00', '-', '240');
  24. INSERT INTO `timezones` VALUES ('10', 'Newfoundland', '03:30:00', '-', '210');
  25. INSERT INTO `timezones` VALUES ('11', 'Brazil, Buenos Aires, Georgetown', '03:00:00', '-', '180');
  26. INSERT INTO `timezones` VALUES ('12', 'Mid-Atlantic', '02:00:00', '-', '120');
  27. INSERT INTO `timezones` VALUES ('13', 'Azores, Cape Verde Islands', '01:00:00', '-', '60');
  28. INSERT INTO `timezones` VALUES ('14', 'Western Europe Time, London, Lisbon, Casablanca', '00:00:00', '+', '0');
  29. INSERT INTO `timezones` VALUES ('15', 'Brussels, Copenhagen, Madrid, Paris', '01:00:00', '+', '60');
  30. INSERT INTO `timezones` VALUES ('16', 'Kaliningrad, South Africa, Istanbul, Athens', '02:00:00', '+', '120');
  31. INSERT INTO `timezones` VALUES ('17', 'Baghdad, Riyadh, Moscow, St. Petersburg', '03:00:00', '+', '180');
  32. INSERT INTO `timezones` VALUES ('18', 'Tehran', '03:30:00', '+', '210');
  33. INSERT INTO `timezones` VALUES ('19', 'Abu Dhabi, Muscat, Baku, Tbilisi', '04:00:00', '+', '240');
  34. INSERT INTO `timezones` VALUES ('20', 'Kabul', '04:30:00', '+', '270');
  35. INSERT INTO `timezones` VALUES ('21', 'Ekaterinburg, Islamabad, Karachi, Tashkent', '05:00:00', '+', '300');
  36. INSERT INTO `timezones` VALUES ('22', 'Bombay, Calcutta, Madras, New Delhi', '05:30:00', '+', '330');
  37. INSERT INTO `timezones` VALUES ('23', 'Kathmandu', '05:45:00', '+', '345');
  38. INSERT INTO `timezones` VALUES ('24', 'Almaty, Dhaka, Colombo', '06:00:00', '+', '360');
  39. INSERT INTO `timezones` VALUES ('25', 'Bangkok, Hanoi, Jakarta', '07:00:00', '+', '420');
  40. INSERT INTO `timezones` VALUES ('26', 'Beijing, Perth, Singapore, Hong Kong', '08:00:00', '+', '480');
  41. INSERT INTO `timezones` VALUES ('27', 'Tokyo, Seoul, Osaka, Sapporo, Yakutsk', '09:00:00', '+', '540');
  42. INSERT INTO `timezones` VALUES ('28', 'Adelaide, Darwin', '09:30:00', '+', '570');
  43. INSERT INTO `timezones` VALUES ('29', 'Eastern Australia, Guam, Vladivostok', '10:00:00', '+', '600');
  44. INSERT INTO `timezones` VALUES ('30', 'Magadan, Solomon Islands, New Caledonia', '11:00:00', '+', '660');
  45. INSERT INTO `timezones` VALUES ('31', 'Auckland, Wellington, Fiji, Kamchatka', '12:00:00', '+', '720');
  46. INSERT INTO `timezones` VALUES ('32', 'Chatham Islands', '12:45:00', '+', '765');
  47. INSERT INTO `timezones` VALUES ('33', 'New Zelanad Daylight Time, Tonga', '13:00:00', '+', '780');

Report this snippet  

You need to login to post a comment.