#1
  1. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    204
    Rep Power
    3

    What date storage to use?


    I'm building a website in which a database is used to save a user specified date..

    Now later I need to work with this date, convert it to seconds, time since other dates and so on.

    What would be the best data storage to use? So that i can still work with this date afterwards.. I was thinking about timestamp?

    thanks
  2. #2
  3. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Hi,

    what database system are you talking about? MySQL? Then use DATETIME.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  4. #3
  5. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    204
    Rep Power
    3
    Jup mySQL. But later i would have to manipulate the date/time with jQuery (javascript) is it possible with this kind of format? thanks for the response
  6. #4
  7. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Not sure what you mean by "format". DATETIME is a data type to hold timestamps -- just like INT holds integers. When fetching the date from the database, you can convert it into any format you want.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  8. #5
  9. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    204
    Rep Power
    3
    Originally Posted by Jacques1
    Not sure what you mean by "format". DATETIME is a data type to hold timestamps -- just like INT holds integers. When fetching the date from the database, you can convert it into any format you want.
    Aha allright perfect. How much characters is a normal timestamp by the way? i had trouble before where a PHP timestamp had a different length then a Js timestamp

    thanks by the way
  10. #6
  11. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    The different timestamp formats JavaScript can parse are described here:

    http://msdn.microsoft.com/en-us/libr...(v=vs.94).aspx
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  12. #7
  13. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    204
    Rep Power
    3
    Originally Posted by Jacques1
    The different timestamp formats JavaScript can parse are described here:

    http://msdn.microsoft.com/en-us/libr...(v=vs.94).aspx
    Hmh don't see anything about timestamps and how javascript can convert them to readable dates? Ideal would be that i store timestamps in the database and JS makes readable dates out of them .. right? haven't found any documentation about that
  14. #8
  15. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    OK, maybe there's a misunderstanding somewhere.

    If you wanna store a timestamp in the database, you use the timestamp type provided by your database system. In the case of MySQL, that's DATETIME.

    A DATETIME value is not a string. It's an abstract value representing a certain point of time. Theoretically, you could store an actual timestamp string, but that's a bad idea, because you won't have any type safety (a string can contain anything).

    You can then convert this value into a string in order to pass it to another language (like JavaScript). To do that, you have to look up the specific format expected by this language. In the case of JavaScript, you need the format described in the link:

    Code:
    YYYY-MM-DDTHH:MM:SS
    (The "T" is a literal character.)

    Use this as a test:

    Code:
    var date = new Date("2013-06-19T13:00:00");
    console.log(date);
    So in your SELECT query, you have to create this particular format with DATE_FORMAT(). And then JavaScript can parse the string and create a Date object from it.

    Comments on this post

    • notflip agrees
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  16. #9
  17. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    204
    Rep Power
    3
    Originally Posted by Jacques1
    OK, maybe there's a misunderstanding somewhere.

    If you wanna store a timestamp in the database, you use the timestamp type provided by your database system. In the case of MySQL, that's DATETIME.

    A DATETIME value is not a string. It's an abstract value representing a certain point of time. Theoretically, you could store an actual timestamp string, but that's a bad idea, because you won't have any type safety (a string can contain anything).

    You can then convert this value into a string in order to pass it to another language (like JavaScript). To do that, you have to look up the specific format expected by this language. In the case of JavaScript, you need the format described in the link:

    Code:
    YYYY-MM-DDTHH:MM:SS
    (The "T" is a literal character.)

    Use this as a test:

    Code:
    var date = new Date("2013-06-19T13:00:00");
    console.log(date);
    So in your SELECT query, you have to create this particular format with DATE_FORMAT(). And then JavaScript can parse the string and create a Date object from it.
    Thanks a lot! it's even simpler then i thought.. I think .. I managed to echo out the database date. And it looks the same then the JS date outputted by your code, i attached a screenshot.

  18. #10
  19. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Well, JavaScript can obviously parse the default timestamp format as used by MySQL. But it's much cleaner to actually transfer the exact format JavaScript wants.

    You never know what MySQL may give you, so better use an explicit format.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  20. #11
  21. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    204
    Rep Power
    3
    Originally Posted by Jacques1
    Well, JavaScript can obviously parse the default timestamp format as used by MySQL. But it's much cleaner to actually transfer the exact format JavaScript wants.

    You never know what MySQL may give you, so better use an explicit format.
    So you mean constructing the date ? like

    Code:
    var d = new Date(year, month, day, hours, minutes, seconds, milliseconds);
  22. #12
  23. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    No, I mean formatting the date in the query:

    Code:
    SELECT DATE_FORMAT(stopdate, 'too-lazy-to-look-it-up') AS stopdate_formatted FROM users ...
    And then you echo $date['stopdate_formatted'] into the JavaScript.

    Just like you did before. The only difference is that you select a specific format instead of relying on MySQL to give you some default format.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  24. #13
  25. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    204
    Rep Power
    3
    Originally Posted by Jacques1
    No, I mean formatting the date in the query:

    Code:
    SELECT DATE_FORMAT(stopdate, 'too-lazy-to-look-it-up') AS stopdate_formatted FROM users ...
    And then you echo $date['stopdate_formatted'] into the JavaScript.

    Just like you did before. The only difference is that you select a specific format instead of relying on MySQL to give you some default format.
    Ahaa i understand! you've been a great help! i'll take a look what date i can output in the query so javascript is happy

IMN logo majestic logo threadwatch logo seochat tools logo