#1
  1. A Change of Season
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    3,558
    Rep Power
    221

    php's date('c') ISO 8601 date


    Hi;

    1 - What is proper column type for php's date('c') ISO 8601 date?

    Code:
    2004-02-12T15:19:21+00:00
    2 - What are the potential issues with varchar 25 ?
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,795
    Rep Power
    4331
    1. DATETIME

    2. inability to do date calculations
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. Backwards Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,905
    Rep Power
    9646
    2. People yelling at you for not using DATETIME.

    Comments on this post

    • SimonJM agrees : LMAO!
  6. #4
  7. A Change of Season
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    3,558
    Rep Power
    221
    Originally Posted by requinix
    2. People yelling at you for not using DATETIME.
    Ok, let's do it the hard way.

    date('c') format (2004-02-12T15:19:21+00:00) is different from mysqls DATETIME format (2018-05-03 00:00:00).

    An example of date('c') would be
    Code:
    2018-05-03T00:00:00+10:00
    (like you don't know).

    How are we gonna store 2018-05-03T00:00:00+10:00 when it expects 2018-05-03 00:00:00?

    Thank you
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,795
    Rep Power
    4331
    Originally Posted by English Breakfast Tea
    Ok, let's do it the hard way.

    How are we gonna store 2018-05-03T00:00:00+10:00 when it expects 2018-05-03 00:00:00?
    i tested various formats, and discovered how to do it

    did you? test anything, i mean
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  10. #6
  11. Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2006
    Posts
    2,682
    Rep Power
    1841
    Store just the date/time using a single time zone. How it is handled on screen (both input and output) is a function of the presentation layer.
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2016
    Location
    Cheshire, UK
    Posts
    90
    Rep Power
    72
    You could reformat it first with
    PHP Code:
    $date DateTime::createFromFormat(DATE_W3C'2004-02-12T15:19:21+00:00')->format('Y-m-d H:i:s'); 
  14. #8
  15. A Change of Season
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    3,558
    Rep Power
    221
    Originally Posted by r937
    i tested various formats, and discovered how to do it

    did you? test anything, i mean
    Yes I tested, trying to store PHPs date('c') in mysql DATETIME field and it shows error => SimplySql.
    Last edited by English Breakfast Tea; May 3rd, 2018 at 05:13 PM.
  16. #9
  17. A Change of Season
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    3,558
    Rep Power
    221
    Originally Posted by Barand
    You could reformat it first with
    PHP Code:
    $date DateTime::createFromFormat(DATE_W3C'2004-02-12T15:19:21+00:00')->format('Y-m-d H:i:s'); 
    User account's timezone is stored in the database with this format.

    There has to be a better way to store it in the database with php timezone.
  18. #10
  19. Backwards Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,905
    Rep Power
    9646
    Originally Posted by English Breakfast Tea
    There has to be a better way to store it in the database with php timezone.
    Not with MySQL.

    Convert all your times to UTC - or Brisbane if you absolutely must - before storing them. If you need to recover the original timezone then store that separately.
  20. #11
  21. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2016
    Location
    Cheshire, UK
    Posts
    90
    Rep Power
    72
    Further to my previous post
    PHP Code:
    $dt DateTime::createFromFormat(DATE_W3C'2004-02-12T15:19:21+02:00');
    $tzone $dt->getTimezone()->getName();
    $dt->setTimezone(new DateTimeZone('UTC'));
    $date $dt->format('Y-m-d H:i:s');
    // now you can store both utc time and timezone 
  22. #12
  23. Backwards Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,905
    Rep Power
    9646

IMN logo majestic logo threadwatch logo seochat tools logo