storing timestamps in mysql

Badgerer

Hi I'm having problems storing timestamps in a myql database..

Here is sql query string I am sending the database :

(37 , 1,0,0,1,'NULL','1090620000'),(1 , 41,0,0,1,'NULL','1091224800'),(44 , 1,0,0,1,'NULL','1091829600'),(1 , 45,0,0,1,'NULL','1092434400'),(46 , 1,0,0,1,'NULL','1093039200'),(1 , 47,0,0,1,'NULL','1093644000'),(48 , 1,0,0,1,'NULL','1094248800'),(1 , 49,0,0,1,'NULL','1094853600'),(51 , 1,0,0,1,'NULL','1095458400'),(51 , 1,0,0,1,'NULL','1096063200'),(37 , 51,0,0,1,'NULL','1096668000'),(51 , 41,0,0,1,'NULL','1097272800'),(44 , 51,0,0,1,'NULL','1097877600'),(51 , 45,0,0,1,'NULL','1098482400'),(46 , 51,0,0,1,'NULL','1099087200'),(51 , 47,0,0,1,'NULL','1099692000'),(48 , 51,0,0,1,'NULL','1100296800'),(51 , 49,0,0,1,'NULL','1100901600'),(51 , 49,0,0,1,'NULL','1101506400'),(49 , 1,0,0,1,'NULL','1102111200'),(37 , 49,0,0,1,'NULL','1102716000'),(49 , 41,0,0,1,'NULL','1103320800'),(44 , 49,0,0,1,'NULL','1103925600'),(49 , 45,0,0,1,'NULL','1104530400'),(46 , 49,0,0,1,'NULL','1105135200'),(49 , 47,0,0,1,'NULL','1105740000'),(48 , 49,0,0,1,'NULL','1106344800'),(48 , 49,0,0,1,'NULL','1106949600'),(51 , 48,0,0,1,'NULL','1107554400'),(48 , 1,0,0,1,'NULL','1108159200'),(37 , 48,0,0,1,'NULL','1108764000'),(48 , 41,0,0,1,'NULL','1109368800'),(44 , 48,0,0,1,'NULL','1109973600'),(48 , 45,0,0,1,'NULL','1110578400'),(46 , 48,0,0,1,'NULL','1111183200'),(48 , 47,0,0,1,'NULL','1111788000'),(48 , 47,0,0,1,'NULL','1112392800'),(47 , 49,0,0,1,'NULL','1112997600'),(51 , 47,0,0,1,'NULL','1113602400'),(47 , 1,0,0,1,'NULL','1114207200'),(37 , 47,0,0,1,'NULL','1114812000'),(47 , 41,0,0,1,'NULL','1115416800'),(44 , 47,0,0,1,'NULL','1116021600'),(47 , 45,0,0,1,'NULL','1116626400'),(46 , 47,0,0,1,'NULL','1117231200'),(46 , 47,0,0,1,'NULL','1117836000'),(48 , 46,0,0,1,'NULL','1118440800'),(46 , 49,0,0,1,'NULL','1119045600'),(51 , 46,0,0,1,'NULL','1119650400'),(46 , 1,0,0,1,'NULL','1120255200'),(37 , 46,0,0,1,'NULL','1120860000'),(46 , 41,0,0,1,'NULL','1121464800'),(44 , 46,0,0,1,'NULL','1122069600'),(46 , 45,0,0,1,'NULL','1122674400'),(46 , 45,0,0,1,'NULL','1123279200'),(45 , 47,0,0,1,'NULL','1123884000'),(48 , 45,0,0,1,'NULL','1124488800'),(45 , 49,0,0,1,'NULL','1125093600'),(51 , 45,0,0,1,'NULL','1125698400'),(45 , 1,0,0,1,'NULL','1126303200'),(37 , 45,0,0,1,'NULL','1126908000'),(45 , 41,0,0,1,'NULL','1127512800'),(44 , 45,0,0,1,'NULL','1128117600'),(44 , 45,0,0,1,'NULL','1128722400'),(46 , 44,0,0,1,'NULL','1129327200'),(44 , 47,0,0,1,'NULL','1129932000'),(48 , 44,0,0,1,'NULL','1130536800'),(44 , 49,0,0,1,'NULL','1131141600'),(51 , 44,0,0,1,'NULL','1131746400'),(44 , 1,0,0,1,'NULL','1132351200'),(37 , 44,0,0,1,'NULL','1132956000'),(44 , 41,0,0,1,'NULL','1133560800'),(44 , 41,0,0,1,'NULL','1134165600'),(41 , 45,0,0,1,'NULL','1134770400'),(46 , 41,0,0,1,'NULL','1135375200'),(41 , 47,0,0,1,'NULL','1135980000'),(48 , 41,0,0,1,'NULL','1136584800'),(41 , 49,0,0,1,'NULL','1137189600'),(51 , 41,0,0,1,'NULL','1137794400'),(41 , 1,0,0,1,'NULL','1138399200'),(37 , 41,0,0,1,'NULL','1139004000'),(1 , 37,0,0,1,'NULL','1139608800'),(41 , 1,0,0,1,'NULL','1140213600'),(1 , 44,0,0,1,'NULL','1140818400'),(45 , 1,0,0,1,'NULL','1141423200'),(1 , 46,0,0,1,'NULL','1142028000'),(47 , 1,0,0,1,'NULL','1142632800'),(1 , 48,0,0,1,'NULL','1143237600'),(49 , 1,0,0,1,'NULL','1143842400'),(1 , 51,0,0,1,'NULL','1144447200'),(1 , 51,0,0,1,'NULL','1145052000'),(51 , 37,0,0,1,'NULL','1145656800'),(41 , 51,0,0,1,'NULL','1146261600'),(51 , 44,0,0,1,'NULL','1146866400'),(45 , 51,0,0,1,'NULL','1147471200'),(51 , 46,0,0,1,'NULL','1148076000'),(47 , 51,0,0,1,'NULL','1148680800'),(51 , 48,0,0,1,'NULL','1149285600'),(49 , 51,0,0,1,'NULL','1149890400'),(49 , 51,0,0,1,'NULL','1150495200'),(1 , 49,0,0,1,'NULL','1151100000'),(49 , 37,0,0,1,'NULL','1151704800'),(41 , 49,0,0,1,'NULL','1152309600'),(49 , 44,0,0,1,'NULL','1152914400'),(45 , 49,0,0,1,'NULL','1153519200'),(49 , 46,0,0,1,'NULL','1154124000'),(47 , 49,0,0,1,'NULL','1154728800'),(49 , 48,0,0,1,'NULL','1155333600'),(49 , 48,0,0,1,'NULL','1155938400'),(48 , 51,0,0,1,'NULL','1156543200'),(1 , 48,0,0,1,'NULL','1157148000'),(48 , 37,0,0,1,'NULL','1157752800'),(41 , 48,0,0,1,'NULL','1158357600'),(48 , 44,0,0,1,'NULL','1158962400'),(45 , 48,0,0,1,'NULL','1159567200'),(48 , 46,0,0,1,'NULL','1160172000'),(47 , 48,0,0,1,'NULL','1160776800'),(47 , 48,0,0,1,'NULL','1161381600'),(49 , 47,0,0,1,'NULL','1161986400'),(47 , 51,0,0,1,'NULL','1162591200'),(1 , 47,0,0,1,'NULL','1163196000'),(47 , 37,0,0,1,'NULL','1163800800'),(41 , 47,0,0,1,'NULL','1164405600'),(47 , 44,0,0,1,'NULL','1165010400'),(45 , 47,0,0,1,'NULL','1165615200'),(47 , 46,0,0,1,'NULL','1166220000'),(47 , 46,0,0,1,'NULL','1166824800'),(46 , 48,0,0,1,'NULL','1167429600'),(49 , 46,0,0,1,'NULL','1168034400'),(46 , 51,0,0,1,'NULL','1168639200'),(1 , 46,0,0,1,'NULL','1169244000'),(46 , 37,0,0,1,'NULL','1169848800'),(41 , 46,0,0,1,'NULL','1170453600'),(46 , 44,0,0,1,'NULL','1171058400'),(45 , 46,0,0,1,'NULL','1171663200'),(45 , 46,0,0,1,'NULL','1172268000'),(47 , 45,0,0,1,'NULL','1172872800'),(45 , 48,0,0,1,'NULL','1173477600'),(49 , 45,0,0,1,'NULL','1174082400'),(45 , 51,0,0,1,'NULL','1174687200'),(1 , 45,0,0,1,'NULL','1175292000'),(45 , 37,0,0,1,'NULL','1175896800'),(41 , 45,0,0,1,'NULL','1176501600'),(45 , 44,0,0,1,'NULL','1177106400'),(45 , 44,0,0,1,'NULL','1177711200'),(44 , 46,0,0,1,'NULL','1178316000'),(47 , 44,0,0,1,'NULL','1178920800'),(44 , 48,0,0,1,'NULL','1179525600'),(49 , 44,0,0,1,'NULL','1180130400'),(44 , 51,0,0,1,'NULL','1180735200'),(1 , 44,0,0,1,'NULL','1181340000'),(44 , 37,0,0,1,'NULL','1181944800'),(41 , 44,0,0,1,'NULL','1182549600'),(41 , 44,0,0,1,'NULL','1183154400'),(45 , 41,0,0,1,'NULL','1183759200'),(41 , 46,0,0,1,'NULL','1184364000'),(47 , 41,0,0,1,'NULL','1184968800'),(41 , 48,0,0,1,'NULL','1185573600'),(49 , 41,0,0,1,'NULL','1186178400'),(41 , 51,0,0,1,'NULL','1186783200'),(1 , 41,0,0,1,'NULL','1187388000'),(41 , 37,0,0,1,'NULL','1187992800');

This all loads fine except for the timestamps (mysql format timestamp(10)) which display as 0000000000.

One of the timestamps does load correctly '1102111200' from (49 , 1,0,0,1,'NULL','1102111200') which seems very strange to me.

Has anyone any suggestions what I might be doing wrong??

Any help would be much appreciated!!

LordShryku

Change the field type to bigint. Those aren't mysql timestamps, they're unix timestamps.

Roger_Ramjet

NO!! Change the column type to TIMESTAMP and it will automatically store the correct time stamp for every record/row you add. ref http://dev.mysql.com/doc/mysql/en/TIMESTAMP_4.1.html

Remeber, you will omit that column from your input since the database fills it in for itself.

LordShryku

I'd say it's pretty obvious he isn't putting in the current timestamp for each of these :rolleyes:

Unix timestamps are easier to work with, but either way would serve the purpose.

Roger_Ramjet

You're right, I didn't look at the data closely myself. Date/time format is the only real criticism I've got with MySQL and the best answer would be a set of functions that convert to more recognised and friendly formats.