Ruby: Mysql timestamp/datetime problem

Question

Is there solution for '0000-00-00 00:00:00' problem, without changing table?

I have "[]" in this query:

dbh.select_all("select j.n, j.name, j.dsc, j.flag, j.td from job j where j.td='0000-00-00 00:00:00'")

I look solution for this: http://rubyforge.org/tracker/index.php?func=detail&aid=22243&group_id=234&atid=967

Can you be clearer about the problem you are having?

Paul Dixon
– Paul Dixon

2009-03-23 15:33:14 +00:00
Commented Mar 23, 2009 at 15:33 — Paul Dixon
– Paul Dixon, Commented Mar 23, 2009 at 15:33
rubyforge.org/tracker/…

amirka
– amirka

2009-03-23 16:07:53 +00:00
Commented Mar 23, 2009 at 16:07 — amirka
– amirka, Commented Mar 23, 2009 at 16:07

Sarah Mei · Accepted Answer · 2009-03-23 16:49:16Z

2

So your default is '0000-00-00 00:00:00' and you can't change it. I dug up my ruby-dbi mailing list archives and found the following explanation:

The problem with the latter case is that it can't be coerced to a DateTime object because it's not a valid time... Honestly, I'm surprised mysql allows it at all. Either way, you'll need to turn type conversion off (see DBI.convert_types=) to get this default value to work or change it and all occurrences of it in your database, or use bind_coltype to treat it as string instead.

See this mailing list archive.

answered Mar 23, 2009 at 16:49

Sarah Mei

18.5k5 gold badges47 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Peter · Accepted Answer · 2009-03-23 15:36:49Z

0

I usually declare my datetimes as accepting NULL, e.g.:

dtime DATETIME NULL DEFAULT NULL

That way I can check if a field IS NULL rather than if a field = '0000-00-00 00:00:00'.

answered Mar 23, 2009 at 15:36

Peter

1,3168 silver badges4 bronze badges

Comments

Michael Andrews · Accepted Answer · 2009-09-22 16:58:51Z

0

You can just cast the "0000-00-00 00:00:00" to NULL:

SELECT IF(mytime="0000-00-00 00:00:00",NULL,mytime) FROM mytable;

answered Sep 22, 2009 at 16:58

Michael Andrews

3252 silver badges4 bronze badges

Collectives™ on Stack Overflow

Ruby: Mysql timestamp/datetime problem

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related