Ruby DateTime and MySQL datetime

Discussion in 'Ruby' started by Josselin, Oct 15, 2006.

  1. Josselin

    Josselin Guest

    I am lost....

    using
    sel_start_date = DateTime.new(sd.year,sd.month,sd.day,16,0,0)
    if I display it I have sel_start_date.to_s => "2006-10-19T16:00:00Z"

    but I cannot performed correct searches into my MySQL DB (4.1.23) on
    datetime columns

    It seems that I need to use strings like "2006-10-19 16:00:00" in the
    WHERE clause...
    ( I tried using manual queries with this format and it runs...)

    what is the best startegy to use datetime correctly between Ruby and MySQL ?

    thanks

    Kad
     
    Josselin, Oct 15, 2006
    #1
    1. Advertising

  2. Use MySQL date functions DATE_FORMAT or FROM_UNIXTIME

    SELECT DATE_FORMAT('1997-10-04 22:23:00', '%D %y %a %d %m %b %j');
     
    äÏÈÁÑ òÙËÁ, Oct 15, 2006
    #2
    1. Advertising

  3. --------------enig4839777F508C7EA667EBB934
    Content-Type: text/plain; charset=ISO-8859-1
    Content-Transfer-Encoding: quoted-printable

    Josselin wrote:
    > what is the best startegy to use datetime correctly between Ruby and
    > MySQL ?
    >=20


    Personally, I'd use timestamps to store the information unless it's
    necessary to preserve the timezone information.

    Mind you, I think there's a gotcha lurking in the MySQL timestamp not
    being the UNIX timestamp per default or something like that.

    David Vallner


    --------------enig4839777F508C7EA667EBB934
    Content-Type: application/pgp-signature; name="signature.asc"
    Content-Description: OpenPGP digital signature
    Content-Disposition: attachment; filename="signature.asc"

    -----BEGIN PGP SIGNATURE-----
    Version: GnuPG v1.4.5 (MingW32)

    iD8DBQFFMuj7y6MhrS8astoRApNtAJ0bEkB/7ml6BqzUTmmTvRQlkS3f0ACfdjFE
    Pr4c0VS7+tcRL+y/GyU/hr8=
    =bnHI
    -----END PGP SIGNATURE-----

    --------------enig4839777F508C7EA667EBB934--
     
    David Vallner, Oct 16, 2006
    #3
  4. David Vallner wrote:
    > Personally, I'd use timestamps to store the information unless it's
    > necessary to preserve the timezone information.
    >
    > Mind you, I think there's a gotcha lurking in the MySQL timestamp not
    > being the UNIX timestamp per default or something like that.
    >
    > David Vallner


    Yes, I used MySQL timestamp, which is better since it initialize itself
    if you don't.

    To Josselin
    You can use a format string such as the following to format your
    datetime

    TimeFmtStr="%Y-%m-%d %H:%M:%S"
    yourdatetimefield.strftime(TimeFmtStr)

    to format it.



    --
    Posted via http://www.ruby-forum.com/.
     
    Roseanne Zhang, Oct 16, 2006
    #4
  5. Re: [OT] Ruby DateTime and MySQL datetime

    --------------enigCCEE99532A0018F423BE2828
    Content-Type: text/plain; charset=UTF-8
    Content-Transfer-Encoding: quoted-printable

    Roseanne Zhang wrote:
    > Yes, I used MySQL timestamp, which is better since it initialize itself=

    =20
    > if you don't.
    >=20


    Which is an idiosyncracy of MySQL I resent, but that's sidetracking.
    Also, it's only convenient to store server-side times, and because of
    the automagical behaviour, at best record creation / update times.

    (Hands up who remembers to set the MySQL connection timezone depending
    on the HTTP request client locale?)

    User-input times will need massaging to convert them from his local
    timezone to a canonical representation anyway, often can be optional,
    when relying on MySQL specifics will shoot yourself in the foot as it's
    completely impossible for the first timestamp column to contain a SQL
    null ever, and a UNIX epoch timestamp is the a representation I'd prefer
    on accounts of being widely supported, compact, and trivial and
    efficient to do comparison and computation with, followed by an ISO
    textual notation on accounts of being as standard as they get.

    David Vallner


    --------------enigCCEE99532A0018F423BE2828
    Content-Type: application/pgp-signature; name="signature.asc"
    Content-Description: OpenPGP digital signature
    Content-Disposition: attachment; filename="signature.asc"

    -----BEGIN PGP SIGNATURE-----
    Version: GnuPG v1.4.5 (MingW32)

    iD8DBQFFMva+y6MhrS8astoRAqztAJ98cr8UnhiS75ozxPm1qwU8CtBIRwCfaVWw
    8RUCrB9IMtkgR/6vJHN8d+o=
    =IARa
    -----END PGP SIGNATURE-----

    --------------enigCCEE99532A0018F423BE2828--
     
    David Vallner, Oct 16, 2006
    #5
  6. Josselin

    Josselin Guest

    Re: [OT] Ruby DateTime and MySQL datetime

    On 2006-10-16 05:04:34 +0200, David Vallner <> said:

    >
    > Roseanne Zhang wrote:
    >> Yes, I used MySQL timestamp, which is better since it initialize itself=

    > =20
    >> if you don't.
    >> =20

    >
    > Which is an idiosyncracy of MySQL I resent, but that's sidetracking.
    > Also, it's only convenient to store server-side times, and because of
    > the automagical behaviour, at best record creation / update times.
    >
    > (Hands up who remembers to set the MySQL connection timezone depending
    > on the HTTP request client locale?)
    >
    > User-input times will need massaging to convert them from his local
    > timezone to a canonical representation anyway, often can be optional,
    > when relying on MySQL specifics will shoot yourself in the foot as it's
    > completely impossible for the first timestamp column to contain a SQL
    > null ever, and a UNIX epoch timestamp is the a representation I'd prefer
    > on accounts of being widely supported, compact, and trivial and
    > efficient to do comparison and computation with, followed by an ISO
    > textual notation on accounts of being as standard as they get.
    >
    > David Vallner
    >
    >
    >
    > This message has one or more attachments. Select "Save Attachments"
    > from the File menu to save.


    Thanks to all of you... Using Rails, in the meantime I found a way to
    format the date before any DB query. But I understand the timezone
    problem if it's necessary to use it (ex : blog -> posts).
    In my particular case create/update methods will always set the time
    (not the server...) but I keep in mind that if it's necessary to rely
    on server time settings then using UNIX timestamp would be the
    solution....
    just for my books , why this difference in datetime format with MySQL ?
    (I mean the T and Z delimiters.... not beeing supported by MySQL... ?
    who has precedence ?

    ActiveSupport::CoreExtensions::Date::Conversions::DATE_FORMATS
    Rails uses the :db formatter when converting a date to a string to use
    in a database query. So you you can change the :db format and the way
    dates are formatted for the database will change automatically

    (Time.now..Time.utc(2006, 12, 24, 09, 00)).to_s:)db)
    #=>'BETWEEN 2005-01-24 15:29:24' AND '2006-12-24 09:00:00'"

    Joss
     
    Josselin, Oct 16, 2006
    #6
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Christos TZOTZIOY Georgiou
    Replies:
    3
    Views:
    711
    Christos TZOTZIOY Georgiou
    Sep 13, 2003
  2. Tim Peters
    Replies:
    0
    Views:
    537
    Tim Peters
    Sep 9, 2003
  3. mp
    Replies:
    1
    Views:
    392
    John Machin
    Jul 28, 2006
  4. Martin
    Replies:
    0
    Views:
    338
    Martin
    Dec 27, 2008
  5. Tobiah
    Replies:
    2
    Views:
    442
    Thomas Rachel
    Jun 3, 2011
Loading...

Share This Page