数値をTIME型に変換して抽出したい

質問

数値をTIME型に変換して抽出したい

少数を含む数値を次のようにTIME型に変換して抽出したいのですが、

CREATE TABLE timetbl (
id INT PRIMARY KEY AUTO_INCREMENT,
time double
) ENGINE=InnoDB;

[timetbl]
id　　time
1　　　3601.999

【得たい結果】
[timetblのid]　[timetblのtimeをTIME形式に]
　　1　　　　　　　　　01:00:01.999

どのようなSQL文を記述すれば良いのでしょうか？

回答No.5

yambejp · ベストアンサー

#4は適当すぎました。
これでいけます。
SELECT time
,left(
time_format(
cast(
truncate(time/3600,0) *10000
+(truncate(time/60,0) -truncate(time/3600,0)*60)*100
+mod(truncate(time,3),60)
as time)
,'%T.%f')
,12) as t
FROM timetbl

これを前のやつに応用するとこんな感じ？
（例示されているt1.tbl_idはt1.idですよね？）

select t1.id as t1id
,sub1.t2name
,t.name
,sub1.t2count
,max(t3.created) as t3_max_created

,min(
left(
time_format(
cast(
truncate(`time`/3600,0) *10000
+(truncate(`time`/60,0) -truncate(`time`/3600,0)*60)*100
+mod(truncate(`time`,3),60)
as time)
,'%T.%f')
,12)
) as t3_min_time

,sub2.t3_aaa_created
from tbl1 as t1
left join tbl as t on t.id=t1.id
left join (
select t1_2.tbl1_id as t1id
,group_concat(t2.name) as t2name
,count(*) as t2count
from tbl1_tbl2 as t1_2
inner join tbl2 as t2 on t1_2.tbl2_id=t2.id
group by t1id
) as sub1 on sub1.t1id=t1.id
left join tbl3 as t3 on t3.tbl1_id=t1.id
left join (
select
tbl1_id,max(created) as t3_aaa_created
from tbl3
inner join tbl on tbl_id=tbl.id
and name='aaa'
group by tbl1_id
) as sub2 on sub2.tbl1_id=t1.id
group by t1id
order by time desc

お礼

ご回答ありがとうございます。
なるほど、そのやり方でできました。
ありがとうございます。

ちなみに
＞time自体が存在しない場合（tbl1のidが2）並べ替えにおいてその行は無視して並べ替える
これは
http://tetsuwo.tumblr.com/post/5572292332/mysql-order-by-null-null
を参考に

order by time is null asc, time asc

こうすることでできました（たぶん・・）

回答No.4

yambejp

>999000の部分を999と3桁にしたい

今回のケースで一番問題だったのはtime型が小数点を持たないことです。
timeでキャストをするとマイクロタイムが適用されるので6桁が表示される
ようですね。time型処理で考えればこれが限界です。

そこで思い切って考え方を変えましょう。
文字列として処理してしまうのであればこんな感じでいけます。

SELECT
time,
CONCAT(
SEC_TO_TIME(truncate(time,0))
,'.',mod(time*1000,1000)
) as t
FROM timetbl

お礼

ご回答ありがとうございます。
なるほど、たしかにこのやり方なら表示上はその通りの結果を得られるのですが、
自分がやりたいと思っているのは、申し訳ないですが、以前に質問させて頂きました
http://oshiete.goo.ne.jp/qa/7077103.html
このテーブル構成のtbl3に含まれるカラムなのです。つまり

CREATE TABLE tbl3 (
id INT PRIMARY KEY AUTO_INCREMENT,
tbl1_id int,
tbl_id int,
time double,
created datetime,
FOREIGN KEY(tbl1_id) REFERENCES tbl1(id),
FOREIGN KEY(tbl_id) REFERENCES tbl(id)
) ENGINE=InnoDB;

[tbl3]
id　　tbl1_id　　created　　　　　　　　　　tbl_id　　time
1　　　1　　　　2011-10-17 01:51:39　　　1　　　　13.1
2　　　1　　　　2011-10-17 02:51:39　　　2　　　　3601.999
3　　　3　　　　2000-10-17 01:51:39　　　1　　　　2

【得たい結果】
[tbl1のid]　[tbl2のnameのリスト]　[・・・（省略）]　　[・・・]　[tbl3の最速のtimeを00:00:00.000形式で]
　　1　　　　　　　　　a,b,b　　　　　　・・・　　　　　　　・・・　　　00:00:13.100
　　2　　　　　　　　　a　　　　　　　　・・・　　　　　　　・・・　　　
　　3　　　　　　　　　a,b　　　　　　　・・・　　　　　　　・・・　　　00:00:02.000

というような表示形式を得たいと思って

select t1.id as t1id
,sub1.t2name
,t.name
,sub1.t2count
,max(t3.created) as t3_max_created

,min(cast(
truncate(time/3600,0) *10000
+(truncate(time/60,0) -truncate(time/3600,0)*60)*100
+mod(truncate(time,3),60)
as time)) as t3_min_time

,sub2.t3_aaa_created
from tbl1 as t1
left join tbl as t on t.id=t1.tbl_id
left join (
select t1_2.tbl1_id as t1id
,group_concat(t2.name) as t2name
,count(*) as t2count
from tbl1_tbl2 as t1_2
inner join tbl2 as t2 on t1_2.tbl2_id=t2.id
group by t1id
) as sub1 on sub1.t1id=t1.id
left join tbl3 as t3 on t3.tbl1_id=t1.id
left join (
select
tbl1_id,max(created) as t3_aaa_created
from tbl3
inner join tbl on tbl_id=tbl.id
and name='aaa'
group by tbl1_id
) as sub2 on sub2.tbl1_id=t1.id
group by t1id

と記述したのですが、[t3_min_time] => 00:00:13と小数部分が表示されなくなりました・・・
（書ききれないので補足を借ります。。）

補足

（続きです。。）
これはどのように記述すれば小数部分も表示されるようになるのでしょうか？
もし仰るように、time型処理で限界があるようでしたら、並べ替えだけはしたいので
6桁（999000）でも結構ですので、コード側などでそれを受け取って文字を切り取ったいと思います。
それと「tbl3の最速のtimeで並べ替える」のですが、time自体が存在しない場合（tbl1のidが2）
並べ替えにおいてその行は無視して並べ替えることは可能でしょうか？つまり
最速の昇順（asc）なら

【得たい結果】
[tbl1のid]　[tbl2のnameのリスト]　[・・・（省略）]　　[・・・]　[tbl3の最速のtimeを00:00:00.000形式で]
　　3　　　　　　　　　a,b　　　　　　　・・・　　　　　　　・・・　　　00:00:02.000
　　1　　　　　　　　　a,b,b　　　　　　・・・　　　　　　　・・・　　　00:00:13.100
　　2　　　　　　　　　a　　　　　　　　・・・　　　　　　　・・・　　　

最速の降順（desc）なら

【得たい結果】
[tbl1のid]　[tbl2のnameのリスト]　[・・・（省略）]　　[・・・]　[tbl3の最速のtimeを00:00:00.000形式で]
　　1　　　　　　　　　a,b,b　　　　　　・・・　　　　　　　・・・　　　00:00:13.100
　　3　　　　　　　　　a,b　　　　　　　・・・　　　　　　　・・・　　　00:00:02.000
　　2　　　　　　　　　a　　　　　　　　・・・　　　　　　　・・・　　　

というような表示結果を得たいです。

回答No.3

yambejp

>[time] => 01:51:39

この時点でおかしいでしょ？
timeは質問者さんが用意したフィールドなんですから3600.999が
出力されないと・・・

timeが予約語とぶつかっている可能性はあるので
とりあえず`time`としてみてはどうでしょうか？

SELECT
`time`
,cast(
truncate(`time`/3600,0) *10000
+(truncate(`time`/60,0) -truncate(`time`/3600,0)*60)*100
+mod(truncate(`time`,3),60)
as time) as t
FROM timetbl

お礼

ご回答ありがとうございます。
仰る通り、こちら側でテーブルのカラムに違った内容が入っていました・・・
正確に言うと、

CREATE TABLE timetbl (
id INT PRIMARY KEY AUTO_INCREMENT,
time TIME
) ENGINE=InnoDB;

になっていて、そこに'01:51:39.999'が入っていて、それで実行していました・・・
なるほど、カラム名timeはバッティングする可能性があるので使わない方がいいですね。。

SELECT
time
,cast(
truncate(time/3600,0) *10000
+(truncate(time/60,0) -truncate(time/3600,0)*60)*100
+mod(truncate(time,3),60)
as time) as t
FROM timetbl

これでほぼ理想通りの結果が得られました。

Array
(
[time] => 3601.999
[t] => 01:00:01.999000
)

すいません、追加なのですが、999000の部分を999と3桁にしたいのですが、
それはどのような記述になるのでしょうか？

回答No.2

yambejp

たとえば・・・

SELECT
time
,truncate(time/3600,0) as h
,truncate(time/60,0) -truncate(time/3600,0)*60 as m
,mod(truncate(time,3),60) as s
FROM timetbl

みたいになるのでしょうか？
であればこれを使って

SELECT
time
,cast(
truncate(time/3600,0) *10000
+(truncate(time/60,0) -truncate(time/3600,0)*60)*100
+mod(truncate(time,3),60)
as time) as t
FROM timetbl

お礼

ご回答ありがとうございます。
こちら

SELECT
time
,cast(
truncate(time/3600,0) *10000
+(truncate(time/60,0) -truncate(time/3600,0)*60)*100
+mod(truncate(time,3),60)
as time) as t
FROM timetbl

を実行したら

Array
(
[time] => 01:51:39
[t] => 04:12:19
)

を得たのですが、自分が求めている結果はtimeが3601.999秒（3601秒　＋　0.999秒　1時間と1.999秒になりますよね？）
なので

01:00:01.999

という表示結果が得たいのですが、つまり

秒数を00（時間）:00（分）:00（秒）.000（少数部分の秒）

このような形式に変換して取得したいのですが、
要はタイムウォッチみたいなやつです。

補足

すいません、こちらのテーブルのカラムに入ってる内容が違っていて
ミスでした・・・

SELECT
time
,cast(
truncate(time/3600,0) *10000
+(truncate(time/60,0) -truncate(time/3600,0)*60)*100
+mod(truncate(time,3),60)
as time) as t
FROM timetbl

これでほぼ理想通りの結果が得られました。

Array
(
[time] => 3601.999
[t] => 01:00:01.999000
)

すいません、追加なのですが、999000の部分を999と3桁にしたいのですが、
それはどのような記述になるのでしょうか？

回答No.1

chie65536（@chie65535）

SELECT id , concat(concat(concat(concat(concat(concat(lpad(mod(truncate(time / 3600,0),24),2,'00') , ':') , lpad(mod(truncate(time / 60,0),60),2,'00')) , ':') , lpad(mod(truncate(time,0),60),2,"00")) , '.') , lpad(mod(time * 1000,1000),3,'000') as formattime
FROM timetbl;

お礼

ご回答ありがとうございます。
すいません、これを実行すると

「 Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM timetbl' at line 1'」

というエラーがでてきてしまうのですが、
どこを修正したら良いのでしょうか？？