Professional Web Applications Themes

MYSQL TIMESTAMP Format - DOS / Linux - MySQL

Hi, I have a query re' the TIMESTAMP format. I've set up identical tables on my DOS machine (Win.XP), and also on my Linux box. Nothing fancy, just a few fields, two of which are TIMESTAMP fields. These are set up as follows: MYDATE1 TIMESTAMP NOT NULL, MYDATE2 TIMESTAMP Each table works fine on both the DOS box and the Linux box. However, on the DOS box the dates are stored without hyphens, but on the Linux box the dates are stored with hyphens. eg DOS = 20070508112803 Linux= 2007-05-16 08:32:56 This is a problem because my scripts assume the ...

  1. #1

    Default MYSQL TIMESTAMP Format - DOS / Linux

    Hi,

    I have a query re' the TIMESTAMP format.

    I've set up identical tables on my DOS machine (Win.XP), and also on
    my Linux box. Nothing fancy, just a few fields, two of which are
    TIMESTAMP fields. These are set up as follows:

    MYDATE1 TIMESTAMP NOT NULL,
    MYDATE2 TIMESTAMP

    Each table works fine on both the DOS box and the Linux box.

    However, on the DOS box the dates are stored without hyphens, but on
    the Linux box the dates are stored with hyphens.

    eg DOS = 20070508112803
    Linux= 2007-05-16 08:32:56

    This is a problem because my scripts assume the first format, although
    the probem is really that the formats are different - either one I can
    live with, just so long as they are the same.

    I'm guessing there must be some way to get MySQL to store the
    TIMESTAMP field, and for that matter DATE fields, in a certain format,
    but I have found nothing to help me so far.

    Any information re' resolving this would be gratefully received.

    Thanks!
    Jane

    janedunnie@gmail.com Guest

  2. #2

    Default Re: MYSQL TIMESTAMP Format - DOS / Linux

    com wrote:
     

    Use DATE_FORMAT, that way you will always be sure you get the dates in the
    format you want.




    --

    //Aho
    J.O. Guest

  3. #3

    Default Re: MYSQL TIMESTAMP Format - DOS / Linux

    On 16 May 2007 08:50:02 -0700, com wrote: 

    You don't need to worry about how it's stored. MySQL will take DOS's
    format[1] and there's an indication from a user note that adding '+0' to
    the name of the column will have the timestamp read out without
    delimiters[2].

    [1] http://dev.mysql.com/doc/refman/5.0/en/datetime.html
    [2] http://dev.mysql.com/doc/refman/5.0/en/timestamp-4-1.html

    --
    29. I will dress in bright and cheery colors, and so throw my enemies into
    confusion.
    --Peter Anspach's list of things to do as an Evil Overlord
    Peter Guest

  4. #4

    Default Re: MYSQL TIMESTAMP Format - DOS / Linux

    Well, thanks for the reply, and I guess from your comment that
    DATE_FORMAT would be used when reading from the table. However, there
    must still be somewhere in the setup that tells MySQL how to save
    dates, etc in certain formats, which is really what I would like to be
    able to find out if possible.

    Meantime I'll play with DATE_FORMAT and see how that works out.

    Thanks!
    Jane


    janedunnie@gmail.com Guest

  5. #5

    Default Re: MYSQL TIMESTAMP Format - DOS / Linux

    Ah, just figured out that the var's I need are as follows:

    mysql> SHOW VARIABLES LIKE "%format%";

    .....
    | date_format | %Y-%m-%d |
    | datetime_format | %Y-%m-%d %H:%i:%s |
    | default_week_format | 0 |
    | time_format | %H:%i:%s |
    .....

    Thanks,
    Jane


    janedunnie@gmail.com Guest

Similar Threads

  1. timestamp (possible with mysql?)
    By Mur-su in forum MySQL
    Replies: 3
    Last Post: February 22nd, 11:46 PM
  2. Translating MySQL timestamp to datetime
    By Joshua Beall in forum PHP Development
    Replies: 1
    Last Post: April 22nd, 02:50 PM
  3. mysql date to timestamp
    By Ben in forum PHP Development
    Replies: 2
    Last Post: December 8th, 05:22 PM
  4. How to format TIMESTAMP column??
    By Blobby J Blobdom in forum PHP Development
    Replies: 3
    Last Post: October 20th, 08:23 AM
  5. mysql or php timestamp arithmatic
    By Christian Calloway in forum PHP Development
    Replies: 0
    Last Post: August 25th, 12:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139