Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Ruby > Ruby DateTime and MySQL datetime

Reply
Thread Tools

Ruby DateTime and MySQL datetime

 
 
Josselin
Guest
Posts: n/a
 
      10-15-2006
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

 
Reply With Quote
 
 
 
 
Дохая Рыка
Guest
Posts: n/a
 
      10-15-2006
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');
 
Reply With Quote
 
 
 
 
David Vallner
Guest
Posts: n/a
 
      10-16-2006
--------------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--

 
Reply With Quote
 
Roseanne Zhang
Guest
Posts: n/a
 
      10-16-2006
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/.

 
Reply With Quote
 
David Vallner
Guest
Posts: n/a
 
      10-16-2006
--------------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+y6MhrS8astoRAqztAJ98cr8UnhiS75ozxPm1qw U8CtBIRwCfaVWw
8RUCrB9IMtkgR/6vJHN8d+o=
=IARa
-----END PGP SIGNATURE-----

--------------enigCCEE99532A0018F423BE2828--

 
Reply With Quote
 
Josselin
Guest
Posts: n/a
 
      10-16-2006
On 2006-10-16 05:04:34 +0200, David Vallner <(E-Mail Removed)> 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:ate::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


 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
datetime.datetime and mysql different after python2.3 Tobiah Python 2 06-03-2011 09:05 AM
[2.4.4] creating a datetime.datetime from an XML xs:dateTime Martin Python 0 12-27-2008 08:08 PM
mx.DateTime to datetime.datetime mp Python 1 07-28-2006 10:57 PM
datetime: .datetime-.datetime = .timedelta, .time-.time=TypeError ? Christos TZOTZIOY Georgiou Python 3 09-13-2003 10:44 AM
RE: datetime: .datetime-.datetime = .timedelta, .time-.time=TypeError ? Tim Peters Python 0 09-09-2003 12:57 AM



Advertisments