mysql のDB内の時刻データを1秒ふやす方法について

Question

前提・実現したいこと

時刻データを１秒増やす方法についてお知恵を貸していただけませんでしょうか？
pythonでmysqlのDBに値を入れております。

発生している問題

下記のようなcsvデータがあり、mysqlのDBへinsertしています。
（id と created_timeは csv からではなく insert時にデータが入っています。
auto_increment と current_timestamp 機能によって）

app1,1,100,2020/10/16 21:37:31
app1,1,800,2020/10/16 21:37:31
app1,1,150,2020/10/16 21:37:31
app1,1,180,2020/10/16 22:30:31
app1,2,200,2020/10/16 22:30:31

id	name(varchar)	value1(int)	value2(int)	get_time(timestamp)	created_time(timestamp)
1	app1	1	100	2020-10-16 21:37:31	2020-10-23 08:24:45
2	app1	1	800	2020-10-16 21:37:31	2020-10-23 08:24:45
3	app1	1	150	2020-10-16 21:37:31	2020-10-23 08:24:45
4	app1	2	180	2020-10-16 22:30:31	2020-10-23 08:24:45
5	app1	2	200	2020-10-16 22:30:31	2020-10-23 08:24:45

###このとき 次の行と比較した場合に value1 の値が同じ場合は、次のデータは +1秒したいのです。
つまり、こういう結果にしたいのです。

id	name(varchar)	value1(int)	value2(int)	get_time(timestamp)	created_time(timestamp)
1	app1	1	100	2020-10-16 21:37:31	2020-10-23 08:24:45
2	app1	1	800	2020-10-16 21:37:32	2020-10-23 08:24:45
3	app1	1	150	2020-10-16 21:37:33	2020-10-23 08:24:45
4	app1	2	180	2020-10-16 22:30:31	2020-10-23 08:24:45
5	app1	2	200	2020-10-16 22:30:32	2020-10-23 08:24:45

該当のソースコード

with codecs.open('CSVファイル','r','utf-8','ignore') as f:
    for line in f:
        name, value1, value2, get_time = map(str, line.split(','))
        cursor.execute('INSERT INTO table_name (name,value1,value2,get_time) values(\'{}\',\'{}\',\'{}\',\'{}\');'.format(name,value1,value2,get_time))

試したこと

変数を1つ用意して、
for文でcsvファイルの行を1行読むたびにその用意した変数に value1 の値をいれ、次回の行を読み込んだ際に比較して、同じ値だったら get_time の値を1つ（1秒）増やそうとしました。
しかしながら、get_timeは文字列のため、値を１すら（1秒）ふやせません。（+1ができない）

補足情報（FW/ツールのバージョンなど）

mysql Ver 8.0.21
python3.8.5
Ubuntu18.04

質問

データベースに投入後の変換　または　データベースに投入前での変換　方法はどちらでも構いませんので、時刻データを１秒増やす方法をご存知の方いらっしゃいましたらお知恵をお借りできませんでしょうか？

よろしくお願い致します。

Comments

[dodox86]

ご提示のコード中、
> name, value1, value2, get_time, created_time = map(str, line.split(','))

ですが、元データのCSVファイル行データは「app1,1,100,2020/10/16 21:37:31」で、4つのカラムしかないので、created_timeはmap()の結果として不要なのではないでしょうか。いずれにせよINSERTでCURRENT_TIMESTAMPがセットされることが前提ということですよね。

[EzrealTrueshot]

おっしゃるとおりです！
必要ありません！

>いずれにせよINSERTでCURRENT_TIMESTAMPがセットされることが前提ということですよね。
はい！

[EzrealTrueshot]

また、今回は current_timestamp がセットされない get_time の方が対象なので、今回の質問から created_timeは除外したほうがよかったかもしれません。
すみませんでした。

Answer 1

ご提示のコードをもとに、データの加工処理のみ示します。実直に処理を行うと例えばこんな感じになるでしょうか。1行ごとにvalue1の値を前の行のものと比較し、違っていればget_timeの値をベースとして、value1の同じ値が続く限り+1秒していきます。+1するのはあらかじめ日時文字列からdatetimeに変換し、timedeltaで加算すればできます。下記コードではファイルの扱いにcodecなど使わず、一般的なものに修正しています。本コードを流用する際は内容を理解した上でINSERT文にするなりしてください。

import datetime

# value1の前のグループの値
value1_pre = None

# value1グループの更新用日時
cur_time = None

# 日時文字列変換用書式  'YYYY/mm/dd HH:MM:SS'
dtformat = '%Y/%m/%d %H:%M:%S'

with open('data.csv', 'r') as f:
	for line in f:
		# 改行を除去して分割
		name, value1, value2, get_time = map(str, line.rstrip().split(','))

		if (value1_pre is None) or (value1 != value1_pre):
			# 最初の行または前の行からvalue1が変わった。
			value1_pre = value1
			cur_time = datetime.datetime.strptime(get_time, dtformat)
		else:
			# 前の行からvalue1が変わっていない。+1秒
			cur_time = cur_time + datetime.timedelta(seconds=1)

		# 日時文字列にする
		dt =  datetime.datetime.strftime(cur_time, dtformat)

		# 再度CSV形式に整形
		r = ','.join([name, value1, value2, dt])
		print(r)

実行例です。

$ cat data.csv
app1,1,100,2020/10/16 21:37:31
app1,1,800,2020/10/16 21:37:31
app1,1,150,2020/10/16 21:37:31
app1,2,200,2020/10/16 22:30:31
app1,2,200,2020/10/16 22:30:32
app1,2,200,2020/10/16 22:30:32
app1,3,200,2020/10/16 22:30:40
app1,4,200,2020/10/16 22:30:59
app1,4,200,2020/10/16 22:30:59
app1,4,200,2020/10/16 22:30:59
app1,5,200,2020/10/16 23:59:59
app1,5,200,2020/10/16 23:59:59

$ python3 t1.py
app1,1,100,2020/10/16 21:37:31
app1,1,800,2020/10/16 21:37:32
app1,1,150,2020/10/16 21:37:33
app1,2,200,2020/10/16 22:30:31
app1,2,200,2020/10/16 22:30:32
app1,2,200,2020/10/16 22:30:33
app1,3,200,2020/10/16 22:30:40
app1,4,200,2020/10/16 22:30:59
app1,4,200,2020/10/16 22:31:00
app1,4,200,2020/10/16 22:31:01
app1,5,200,2020/10/16 23:59:59
app1,5,200,2020/10/17 00:00:00

データベースに投入後の変換　または　データベースに投入前での変換　方法はどちらでも構いませんので、

行ごとの逐次処理が必要なように思うので、データベースに投入した後で+1秒していくのはUPDATEでのSQL文一発で修正するのは不可能か、面倒もしくは難しい気がします。ですので、投入前にあらかじめデータを加工しておくのが簡単で順当な方法だと考えます。

Comments

[dodox86]

私だったら、ですが、DBにデータを投入するような場合、スクリプトでは元のCSVファイルを加工しつつINSERTしていくのではなく、加工だけして投入用CSVデータの作成のみするようにします。で、多くのDB製品に付属しているCSVファイルからのインポート機能を使います。その方がデータの確認や問題が起きたときの切り分けがし易いです。

[dodox86]

投稿した後で気が付きましたが、value1のカラムの値はnullであったり空文字列であったりはしないであろうから、Noneで初期化する必要もないか、と思い直しました。ですので私のコード、以下の部分
> value_pre = None
> ...
> if (value1_pre is None) or (value1 != value1_pre):

は、
value_pre = ''
...
if value1 != value1_pre:

で充分かと思います。これも動作確認しました。（Noneを使う方が冗長で、ちょっと意図が見えないコードかもしれません）BAをいただき、質問は閉じたのでコードは訂正せず、そのままにしておきます。

Answer 2

以下の3 Stepで実現できそうですね

1. 文字列を日時(Datetime 型)に変更

参考: https://www.programiz.com/python-programming/datetime/strftime
2. 1秒追加
参考: https://intellipaat.com/community/14186/what-is-the-standard-way-to-add-n-seconds-to-datetime-time-in-python#:~:text=Yes%2C%20in%20Python%20we%20have,just%20get%20the%20time%20value.
3. 日時を文字列に戻す
参考: https://www.journaldev.com/23365/python-string-to-datetime-strptime