#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2002
    Location
    Spain
    Posts
    3
    Rep Power
    0

    Sqlite to mysql, DATETIME format


    Hi,
    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:

    PHP Code:
    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 `DataTableVALUES(2456387.9266319443,0,0,0,0,0,0,0,0,0);
    INSERT INTO `DataTableVALUES(2456410.1797800926,31,2,166,8,11,12,4,9,24); 
    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.

    Thanks in advance.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    Originally Posted by mikebr
    The CURRENT_TIMESTAMP just doesn't make any sence,
    it represents a value in yyyy-mm-dd hh:mi:ss format

    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2002
    Location
    Spain
    Posts
    3
    Rep Power
    0
    Originally Posted by r937
    it represents a value in yyyy-mm-dd hh:mi:ss format

    the timestamp values you posted are sqlite's internal julian date format (URL address blocked) 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
    Thanks for the reply.

    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...

    PHP Code:
    $julDate 2456493.3646875;

    //print_r(cal_from_jd($julDate, CAL_GREGORIAN));
    $date_ARRAY cal_from_jd($julDateCAL_GREGORIAN);
    echo 
    $date_ARRAY['date']; 
    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.

    BTW, the following also works:

    PHP Code:
    $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>'
    Unix timestamp: 1374235200
    Readable date: Friday Jul 19, 2013

    but again uses the first part of the julian date stamp.
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2002
    Location
    Spain
    Posts
    3
    Rep Power
    0
    No worries, thanks for the reply.

IMN logo majestic logo threadwatch logo seochat tools logo