July 20th, 2013, 11:51 AM
Sqlite to mysql, DATETIME format
I am converting a sqlite data file to mysql, I use the sqlite plugin with Firefox and when I run the file through the plugin all looks fine except the DATETIME looks odd and I just cannot understand it, I wonder is there something I am missing here?
The DATA is as follows:
The CURRENT_TIMESTAMP just doesn't make any sence, hopefully someone can shine some light on a timestamp of '2456410.1797800926' so I can convert it to a MySQL timestamp.
CREATE TABLE "DataTable" ("datetime" DATETIME DEFAULT CURRENT_TIMESTAMP, "cT" DOUBLE, "lC" DOUBLE, "crTotal" INTEGER, "tETotal" DOUBLE, "oETotal" DOUBLE, "fCTotal" DOUBLE, "tECTotal" DOUBLE, "oECTotal" DOUBLE, "fCCTotal" DOUBLE);
INSERT INTO `DataTable` VALUES(2456387.9266319443,0,0,0,0,0,0,0,0,0);
INSERT INTO `DataTable` VALUES(2456410.1797800926,31,2,166,8,11,12,4,9,24);
Thanks in advance.
July 20th, 2013, 12:00 PM
it represents a value in yyyy-mm-dd hh:mi:ss format
Originally Posted by mikebr
the timestamp values you posted are sqlite's internal julian date format (see http://en.wikipedia.org/wiki/Julian_day) and i have no idea how you would go about converting them in mysql, so you perhaps could look into getting your plugin to generate a different format
July 20th, 2013, 01:28 PM
Thanks for the reply.
Originally Posted by r937
I understood a julian timestamp to be like 2454522 and not 2456387.9266319443 but now see after some searching that the format 2456387.9266319443 is aslo correct, so...
actually gives me the date as 7/19/2013 but this is only taking the 2456493 as the value for the calculation, which I guess is OK for my needs. The 3646875 after the period must be the time part of the DATETIME as your reply also suggests.
$julDate = 2456493.3646875;
$date_ARRAY = cal_from_jd($julDate, CAL_GREGORIAN);
BTW, the following also works:
Unix timestamp: 1374235200
$julDay = 2456493;
$unixTimeStamp = ($julDay - 2440587.5) * 86400;
echo 'Unix timestamp: ' . $unixTimeStamp . '<br>';
$date_added = date('l M j, Y', $unixTimeStamp);
echo 'Readable date: ' . $date_added . '<br><br>';
Readable date: Friday Jul 19, 2013
but again uses the first part of the julian date stamp.
July 20th, 2013, 02:25 PM
everything you just said went whoosh over my head (as i'm a coldfusion guy, not a php guy) but i could at least appreciate what you were doing
July 21st, 2013, 04:39 AM
No worries, thanks for the reply.