MySQL で出退勤の列を datetime 型で持った場合、列を使用して DATEDIFF と TIMEDIFF で直接計算

以下のような単純な表を考えた場合、当日の勤務時間の計算としては、TIMEDIFF 関数を使います

create table `出退勤トラン` (
	`社員コード` varchar(4)
	,`日時` date
	,`出勤` datetime
	,`退勤` datetime
	,primary key(`社員コード`)
)

TIMEDIFF と DATEDIFF は、それぞれ時間と日付に対して演算を行います。結果として TIMEDIFF は、時刻表示で経過時間をあらわすため、23:59:59 より大きい経過を実行しようとするとエラーになります。
DATEDIFF は経過日数を整数で表すのですが、日付部分が変わると、経過時間が 1 秒でも 1 日を返します。

select 
	社員コード,
	退勤,
	出勤,
	TIMEDIFF(退勤,出勤) as 経過時間,
	DATEDIFF(退勤,出勤) as 経過日数
 from 出退勤トラン

▼ SQL 実行の結果

社員コード	退勤	出勤	経過時間	経過日数
0001	2014/10/12 16:27:44	2014/10/11 16:27:45	2014/10/11 23:59:59	1
0002	2014/10/11 16:27:46	2014/10/11 16:27:45	2014/10/11 0:00:01	0
0003	2014/10/12 0:00:01	2014/10/11 16:27:45	2014/10/11 7:32:16	1
0004	2014/10/11 23:59:59	2014/10/11 16:27:45	2014/10/11 7:32:14	0
0005	2014/10/12	2014/10/11 23:59:59	2014/10/11 0:00:01	1

※ 経過時間の日付部分は、SQLを実行した日の日付です

MySQL を UTF-8 環境で作成して、mysqldump.exe でバックアップするバッチファイルのサンプル

Windows では、MySQL は UTF-8 環境で作成されますが(MySQL 5.6 (Generally Available (GA) Releases) インストール)、クライアントを cp932(shift_jis) としてテーブルを作成し(MySQL 5.6 : テストデータ自動作成スクリプト)、mysqldump.exe で shift_jis のテーブル名を指定して実行すると、そのままでは認識してくれません。

--default-character-set=cp932 をコマンドラインに追加すると、テーブル名を認識してくれますが、出力されたデータは SHIFT_JIS と UTF-8 が混在になっています。( データ部分は SHIFT_JIS で、CREATE 文部分は UTF-8 )

エディタで、個別に該当のキャラクタセットで表示してやるとどちらも使えますが、--default-character-set=cp932 を指定せずに、テーブル指定無しで全て出力してやると、出力されたデータは全て UTF-8 になります。

※ cp932 にしないと、いくつかの文字が正しく変換されずに ? と表示されます( sjis だとそうなります )

setlocal
set run_path=C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqldump.exe
set save_path=%temp%\mysqldump.sql
set save_path_table=%temp%\mysqldump_table.sql
set save_tables=商品マスタ 社員マスタ

REM 全てのテーブルとビュー( 全て、UTF-8 で書き込まれます )
"%run_path%" --host=localhost --user=root --password=trustno1 --add-drop-table --force --quote-names lightbox > "%save_path%" & explorer /select,"%save_path%"

REM テーブル指定( CREATE 文部分のみが、UTF-8 で書き込まれます )
"%run_path%" --host=localhost --user=root --password=trustno1 --add-drop-table --force --quote-names --default-character-set=cp932 lightbox %save_tables% > "%save_path_table%" & explorer /select,"%save_path_table%"

▼ SHIFT_JIS 環境のエディタ内で、UTF-8 として表示された CREATE 文

--
-- Table structure for table `商品マスタ`
--

DROP TABLE IF EXISTS `商品マスタ`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `蝠・刀繝槭せ繧ｿ` (
  `蝠・刀繧ｳ繝ｼ繝荏 varchar(4) NOT NULL DEFAULT '',
  `蝠・刀蜷港 varchar(50) DEFAULT NULL,
  `蝨ｨ蠎ｫ隧穂ｾ｡蜊倅ｾ｡` int(11) DEFAULT NULL,
  `雋ｩ螢ｲ蜊倅ｾ｡` int(11) DEFAULT NULL,
  `蝠・刀蛻・｡杼 varchar(3) DEFAULT NULL,
  `蝠・刀蛹ｺ蛻・ varchar(1) DEFAULT NULL,
  `菴懈・譌･` datetime DEFAULT NULL,
  `譖ｴ譁ｰ譌･` datetime DEFAULT NULL,
  PRIMARY KEY (`蝠・刀繧ｳ繝ｼ繝荏)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

MySQL 5.6 : テストデータ自動作成スクリプト

2014/05/10 : DROP TABLE IF EXISTS 構文を追加し、charset に cp932をセットしました
2014/05/09 : ダイアログのラベル表記を XN Resource Editor(日本語版)で変更しました

※ MySQL のインストールと動作確認は、以下を参照して下さい。

MySQL 5.6.11(Generally Available (GA) Releases) インストール

スクリプトパッケージは以下になります
( mysql-installer-community-5.6.17.0.msi でテスト済みです )

ダウンロード


事前にデータベースを作成しておく必要がありますが、既存の一般データベースでもかまいません。
( ※再作成する場合は、ビューを全て削除し、テーブルを全て削除して下さい )

前述のリンク先の『動作確認』で使用した『SQLの窓 Build C++』で mysql に接続して、右のテキストエリアで create database lightbox と入力して『act』ボタンをクリックすると lightbox データベースが作成されます。( データベース名は 英字で適当に命名して下さい )



createHanbaiB.bat を実行するだけですが、MySQL ODBC 5.2w Driver MySQL ODBC 5.3 Unicode Driver がインストールされている必要があります( 前述のリンク先の内容でインストールするとインストールされます )

※ 現在 MySQL ODBC 5.3 Unicode Driver が最新です(mysql-connector-odbc-5.3.2-win32.msi)
ドライバ名が異なる場合は、process.wsf の 74 行目の "{MySQL ODBC 5.3 Unicode Driver}" を変更して下さい。
( レジストリの HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI に正しい文字列でツリーがあります )

関連する記事

MySQL のインストールと Windows7 64ビットの ODBC と 32ビットの ODBC





名前は組み合わせでランダムで作成しているので、スクリプト内を変更するとバリエーションをカスタマイズ可能です。

▼ コマンドプロンプトで実行





charset は、SQLの窓 Build C++ の内部が SHIFT_JIS なので必要です。内部表現が Unicode のアプリケーションでは必要ありません( MySQL が utf-8 の場合 )


関連する記事

PHP + MySQL : Windows の COM を使った ODBC 経由での接続とデータの取得(SELECT)

extension=php_com_dotnet.dll によって、Windows では、PHP が COM を使えるようになります。他の ODBC 経由と同じく、SHIFT_JIS 前提となりますが、本来の Fields コレクションの文字列での参照がうまく行かないので、連想配列を作成して利用の準備をしています。

PHP で COM を使うような状況は、VBScript のような Windows 純正のスクリプトを使う代わりに、PHP を Windows の保守に使うという選択肢です。そうすると、COM と PHP の関数群の両方が使えるようになります。

<?php
header( "Content-Type: text/html; Charset=shift_jis" );
header( "pragma: no-cache" );
header( "Expires: Wed, 31 May 2000 14:59:58 GMT" );
header( "Cache-control: no-cache" );

// 日本語設定
mb_language( "ja" );
// 内部エンコード
mb_internal_encoding("UTF-8");

// 接続オブジェクト
$cn = new COM( "ADODB.Connection" );
// レコードセットオブジェクト
$rs = new COM( "ADODB.Recordset" );

$server = 'localhost';
$db_name = 'lightbox';
$user = 'root';
$password = 'パスワード';

// 接続文字列作成
$connect_string = "Provider=MSDASQL;";
$connect_string .= "Driver={MySQL ODBC 5.3 Unicode Driver};";
$connect_string .= "Server={$server};";
$connect_string .= "DATABASE={$db_name};";
$connect_string .= "UID={$user};";
$connect_string .= "PWD={$password};";
$connect_string .= "charset=cp932;";

// 接続
try {
	$cn->Open( $connect_string );
}
catch ( Exception $ex ) {
	die( '{"error": "接続できませんでした: ' . $ex->getMessage() . '"}' );
}


$query = "select * from 社員マスタ";

try {
	// レコードセット取得
	$rs->Open( $query, $cn );
}
catch ( Exception $ex ) {
	die( '{"error":' . $ex->getMessage() . '"}' );
}

// 列数
$field_count = $rs->Fields->Count;
$count = 0;

?>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=shift_jis">
<style>
* {
	font-family: "メイリオ", Meiryo, "ＭＳ Ｐゴシック", sans-serif;
	font-size: 12px;
}
table {
	border-collapse: collapse;
	border-style: solid;
	border-color: #c0c0c0;
	border-width: 1px;
	background-color: #FFFFFF;
}
td {
	padding: 5px;
	border-style: solid;
	border-color: #c0c0c0;
	border-width: 1px;
}
</style>
</head>
<body>

<?php
$log_file = "rowdata_006.log";

file_put_contents( $log_file,"" );

print "<table>\n";
while (!$rs->EOF) {
	print "<tr>\n";
	print "\t<td>" . ($count + 1) . "</td>\n";
	for( $i = 0; $i < $field_count; $i++ ) {
		$wk = $rs->Fields[$i]->value."";
		print "\t<td>{$wk}</td>\n";

		// 連想配列作成
		$field[$rs->Fields[$i]->name] = $rs->Fields[$i]->value."";
	}
	print "</tr>\n";
	$count++;

	file_put_contents( $log_file, print_r($field,true), FILE_APPEND );
	$rs->MoveNext();
}
print "</table>";

// レコードセットを閉じる
$rs->Close();

// 接続解除
@$cn->Close();
?>

<br>
出力件数 : <?= $count ?>

関連する記事

VBScript + MySQL : ADO( ODBC ) による接続とデータの取得(SELECT)

VBScript の形態は、大きく分けて3つあり、一つは単純な一枚のテキストに全てのスクリプトを書いて実行する拡張子が .vbs のもの。さらに、外部にスクリプトソースを分割する事ができる本格的なプログラミングをする人の為にある拡張子 .wsf のもの。そして、ここでは紹介しませんが、HTML で画面を作成し、ブラウザの中での動きと同じような環境で作成する拡張子 .hta の HTMLアプリケーションがあります。

MySQL に接続して csv ファイルを書き出すだけの処理を .vbs と .wsf で書いています。.wsf で特別な事をしていないのでここではどちらがという事がありませんが、.wsf のほうがいろいろ作法があり、複雑なことが可能になっています。

ADO は VBScript からアクセス可能な、主にデータベースを扱う為にあるオブジェクトです。通常、データーベースへのアクセスは、ADODB.Connection と ADODB.Recordset でほとんどの事を実行する事ができます。

VBScript で記述する場合の最大特徴は、エラー処理で、on error resume next と on error goto 0 の間でエラーが発生した場合に強制終了を回避できるようになっています。

※ Scripting.FileSystemObject は、テキストファイルを扱う為のオブジェクトです

ado_std.vbs

Set Cn = CreateObject( "ADODB.Connection" )
Set Rs = CreateObject( "ADODB.Recordset" )
Set Fs = CreateObject( "Scripting.FileSystemObject" )

strDriver = "{MySQL ODBC 5.3 Unicode Driver}"
strServer = "localhost"
strDb = "lightbox"
strUser = "root"
strPass =  "パスワード"

ConnectionString = _
	"Provider=MSDASQL" & _
	";Driver=" & strDriver
ConnectionString = ConnectionString & ";Server=" & strServer
ConnectionString = ConnectionString & ";Database=" & strDb
ConnectionString = ConnectionString & ";UID=" & strUser
ConnectionString = ConnectionString & ";PWD=" & strPass
ConnectionString = ConnectionString & ";"

' **********************************************************
' 接続
' **********************************************************
on error resume next
Cn.Open ConnectionString
if Err.Number <> 0 then
	Wscript.Echo Err.Description
	Wscript.Quit
end if
on error goto 0

' **********************************************************
' クエリー
' **********************************************************
Query = "select * from 社員マスタ"

on error resume next
Rs.Open Query, Cn
if Err.Number <> 0 then
	Cn.Close
	Wscript.Echo Err.Description
	Wscript.Quit
end if
on error goto 0

' **********************************************************
' 出力ファイルオープン
' **********************************************************
Set Csv = Fs.CreateTextFile( "ado_std.csv", True )

' **********************************************************
' データ出力
' **********************************************************
Do While not Rs.EOF
	Buffer = ""
	For i = 0 to Rs.Fields.Count - 1
		if Buffer <> "" then
			Buffer = Buffer & ","
		end if
		Buffer = Buffer & """" & Rs.Fields(i).Value & """"
	Next
	Csv.WriteLine Buffer
	Rs.MoveNext
Loop

' **********************************************************
' ファイルクローズ
' **********************************************************
Csv.Close

' **********************************************************
' レコードセットクローズ
' **********************************************************
Rs.Close

' **********************************************************
' 接続解除
' **********************************************************
Cn.Close

Wscript.Echo "処理が終了しました"

ado_pro.wsf

<JOB>
<COMMENT>
************************************************************
■著作権その他

このプログラムはフリーです。どうぞ自由に御使用ください。
著作権は作者である私(lightbox)が保有しています。
また、本ソフトを運用した結果については、作者は一切責任を
負えせんのでご了承ください。
************************************************************
</COMMENT>

<OBJECT id="Cn" progid="ADODB.Connection" />
<OBJECT id="Rs" progid="ADODB.Recordset" />
<OBJECT id="Fs" progid="Scripting.FileSystemObject" />

<SCRIPT language="VBScript">

strDriver = "{MySQL ODBC 5.3 Unicode Driver}"
strServer = "localhost"
strDb = "lightbox"
strUser = "root"
strPass =  "パスワード"

ConnectionString = _
	"Provider=MSDASQL" & _
	";Driver=" & strDriver
ConnectionString = ConnectionString & ";Server=" & strServer
ConnectionString = ConnectionString & ";Database=" & strDb
ConnectionString = ConnectionString & ";UID=" & strUser
ConnectionString = ConnectionString & ";PWD=" & strPass
ConnectionString = ConnectionString & ";"

' **********************************************************
' 接続
' **********************************************************
on error resume next
Cn.Open ConnectionString
if Err.Number <> 0 then
	Wscript.Echo Err.Description
	Wscript.Quit
end if
on error goto 0

' **********************************************************
' クエリー
' **********************************************************
Query = "select * from 社員マスタ"

on error resume next
Rs.Open Query, Cn
if Err.Number <> 0 then
	Cn.Close
	Wscript.Echo Err.Description
	Wscript.Quit
end if
on error goto 0

' **********************************************************
' 出力ファイルオープン
' **********************************************************
Set Csv = Fs.CreateTextFile( "ado_pro.csv", True )

' **********************************************************
' データ出力
' **********************************************************
Do While not Rs.EOF
	Buffer = ""
	For i = 0 to Rs.Fields.Count - 1
		if Buffer <> "" then
			Buffer = Buffer & ","
		end if
		Buffer = Buffer & """" & Rs.Fields(i).Value & """"
	Next
	Csv.WriteLine Buffer
	Rs.MoveNext
Loop

' **********************************************************
' ファイルクローズ
' **********************************************************
Csv.Close

' **********************************************************
' レコードセットクローズ
' **********************************************************
Rs.Close

' **********************************************************
' 接続解除
' **********************************************************
Cn.Close

Wscript.Echo "処理が終了しました"

</SCRIPT>
</JOB>

現在、VBScript の進化は Microsoft 内では止まって(終わって)おり、後継としては、Framework の使える PowerShell になっていますが、かなり敷居の高いものとなっているため、今でも VBScript の範囲でできる事が多いせいもあって、VBScript が使われているところは多いと思います。



関連する記事

PHP + MySQL : Windows における ODBC 経由での接続とデータの取得(SELECT)

MySQL の Windows における ODBC ドライバは、既存の SHIFT_JIS ベースの古いアプリケーシヨンの為に、charset 変更できるのがデータ部分だけとなっていました。こちらから送る SQL 内のキャラクタセットは 日本語環境においては SHIFT_JIS でやりとりするような前提で設計されているようです。

ですから、PHP で利用するとなると、ソースコードを SHIFT_JIS として、ドライバの charset を cp932 にすると全て日本語環境でうまくいくようになっています。もし、UTF-8 ベースで PHP のコードを書く必要がある場合は、charset を utf8 にすると、データは正しく utf8 で読み込めます。ただ、SQL を SHIFT_JIS で書く必要がありますし、列名(連想配列内)は SHIFT_JIS なので、mb_convert_encoding で変換する必要があります。

PDO_ODBC > ODBC 関数

ODBC 関数は、古くからある関数で、他の MySQL 関数に比べると実装(機能)が分散されていて、同じ処理をする場合にコード量が増えてしまいます。しかし、SQLServer に接続して処理を行う場合は信頼性が高いようです。( SQLServer 用の ODBC ドライバは選択肢としてとても有効だと思います )

ただ、MySQL をターゲットとする場合は、わさわざ ODBC を選ぶ必要はほとんどありません。

ODBC 関数

<?php
header( "Content-Type: text/html; Charset=shift_jis" );
header( "pragma: no-cache" );
header( "Expires: Wed, 31 May 2000 14:59:58 GMT" );
header( "Cache-control: no-cache" );

$server = 'localhost';
$db_name = 'lightbox';
$user = 'root';
$password = 'パスワード';

$connect_string = "Provider=MSDASQL;";
$connect_string .= "Driver={MySQL ODBC 5.3 Unicode Driver};";
$connect_string .= "Server={$server};";
$connect_string .= "DATABASE={$db_name};";
$connect_string .= "UID={$user};";
$connect_string .= "PWD={$password};";
$connect_string .= "charset=cp932;";
// このキャラクタ設定は、サーバーからのキャラクタセット
// クライアントは SHIFT_JIS が前提で、インストールされた PCに依存しているようです。

// 接続
$connect = @odbc_connect($connect_string, "", "");
if ( !$connect ) {
	die("接続エラーです : " . odbc_errormsg() );
}

// クエリ
$result = @odbc_exec($connect,"select * from 社員マスタ");
if ( !$result ) {
	die('クエリーに誤りがあります : ' . odbc_errormsg() );
}

// 列数
$field_count = odbc_num_fields( $result );
$count = 0;

?>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=shift_jis">
<style>
* {
	font-family: "メイリオ", Meiryo, "ＭＳ Ｐゴシック", sans-serif;
	font-size: 12px;
}
table {
	border-collapse: collapse;
	border-style: solid;
	border-color: #c0c0c0;
	border-width: 1px;
	background-color: #FFFFFF;
}
td {
	padding: 5px;
	border-style: solid;
	border-color: #c0c0c0;
	border-width: 1px;
}
</style>
</head>
<body>

<?php
print $connect_string;

$log_file = "rowdata_003.log";

file_put_contents( $log_file,"" );

print "<table>\n";
while (odbc_fetch_into($result,$row)) {
	print "<tr>\n";
	print "\t<td>" . ($count + 1) . "</td>\n";
	for( $i = 0; $i < $field_count; $i++ ) {
		print "\t<td>{$row[$i]}</td>\n";
	}
	print "</tr>\n";
	$count++;
	file_put_contents( $log_file, print_r($row,true), FILE_APPEND );
}
print "</table>";

// メモリを開放ですが、通常は必要ありません
odbc_free_result($result);

// 接続解除
odbc_close($connect);
?>

<br>
出力件数 : <?= $count ?>

PDO_ODBC

<?php
header( "Content-Type: text/html; Charset=shift_jis" );
header( "pragma: no-cache" );
header( "Expires: Wed, 31 May 2000 14:59:58 GMT" );
header( "Cache-control: no-cache" );

$conf = array(
	"driver" => "{MySQL ODBC 5.3 Unicode Driver}",
	"server" => "localhost",
	"db" => "lightbox",
	"user" => "root",
	"pass" => "パスワード",
	"charset" => "cp932"
);

// **********************************************************
// 接続
// **********************************************************
try {
	$connect = new PDO(
		"odbc:Driver={$conf['driver']};Server={$conf['server']};" .
		"Database={$conf['db']};Uid={$conf['user']};Pwd={$conf['pass']};".
		"charset={$conf['charset']}");
}
catch ( Exception $ex ) {
	die( '{"error": "接続できませんでした: ' . $ex->getMessage() . '"}' );
}

// **********************************************************
// 結果セット
// **********************************************************
$rs = $connect->query( "select * from 社員マスタ" );
if ( $rs === false ) {
	$er = $connect->errorInfo();
	die('{ "error" : "' . $er[2] . '" }');
}

// フィールド数
$field_count = $rs->columnCount();
$count = 0;

?>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=shift_jis">
<style>
* {
	font-family: "メイリオ", Meiryo, "ＭＳ Ｐゴシック", sans-serif;
	font-size: 12px;
}
table {
	border-collapse: collapse;
	border-style: solid;
	border-color: #c0c0c0;
	border-width: 1px;
	background-color: #FFFFFF;
}
td {
	padding: 5px;
	border-style: solid;
	border-color: #c0c0c0;
	border-width: 1px;
}
</style>
</head>
<body>

<?php
print $connect_string;

$log_file = "rowdata_004.log";

file_put_contents( $log_file,"" );

print "<table>\n";
while ($row = $rs->fetch(PDO::FETCH_BOTH)) {
	print "<tr>\n";
	print "\t<td>" . ($count + 1) . "</td>\n";
	for( $i = 0; $i < $field_count; $i++ ) {
		print "\t<td>{$row[$i]}</td>\n";
	}
	print "</tr>\n";
	$count++;
	file_put_contents( $log_file, print_r($row,true), FILE_APPEND );
}
print "</table>";

$rs->closeCursor();
?>

<br>
出力件数 : <?= $count ?>

関連する記事