datetime型をunixtimeに変換

MySQLにおいて表題の通り。以前はunixtime(エポック秒)を人が理解しやすい表記に変換したが今回は逆である。

■ 環境

  • MySQL 5.6.35

■ unix_timestamp

datetime型のカラムが存在する。これをunixtimeで取得するには下記のように”unix_timestamp()“を使用すれば良い。

mysql> desc users;
+------------------------+--------------+------+-----+---------+----------------+
| Field                  | Type         | Null | Key | Default | Extra          |
+------------------------+--------------+------+-----+---------+----------------+
| id                     | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| name                   | varchar(255) | YES  |     | NULL    |                |
  :
| created_at             | datetime     | NO   |     | NULL    |                |
| updated_at             | datetime     | NO   |     | NULL    |                |
  :

mysql>

ここから”created_at“をunixtimeで取得したい。

mysql> select id, name, unix_timestamp(created_at) from users;
+----+------------+------------+
| id | name       | created_at |
+----+------------+------------+
|  1 | test       | 1512623164 |
|  2 | test       | 1512975404 |
  :
mysql>

以上。

■ 関連

unixtimeをSQLで変換

mysqldump: Got errno 32 on write

表題のエラーが出た。

■ 環境

  • MySQL 5.6.34
  • Linux

■ mysqldump

データをバックアップしようとして下記のようなコマンドで実行しようとした。

$ mysqldump -uXXXXX -pXXXXX -hXXXXXXXX database | gzip > backup/$(date '+%Y%m%d').dump.gz

このように実行しようとしたところ、表題のエラーメッセージが出力された。あれ?と思ったが単に出力先のディレクトリを作成していないだけであった…。”backup“ディレクトリに出力しようとしていたのを忘れていたようだ。

ディレクトリに出力するのであれば、事前にちゃんと作成しておこう。またそのディレクトリに書き込み権限があるかも確認しておこう。

以上。

SQLで文字列の長さを取得

MySQLにおいて表題の通り。ド忘れしたのでメモしておく。

■ 環境

  • MySQL 3.6.5

■ SQL

カラムの型が”varchar“の時に下記のように取得した。

mysql> select length(カラム) from テーブル;

これで取得する対象のカラムに入っているデータが英数字等であれば単純に文字数が取得できる。しかし入っているデータが日本語等の文字であった場合かつテーブルの定義で”utf8“であったりすると、1文字が3byteなので3倍の数値が返ってきた。

ちなみにMySQLでは純粋に文字列数を取得するには”char_length“で返ってくるようだ。

mysql> select char_length(カラム) from テーブル;

こっちだとイメージ通りである。

ちなみに、、自身は”AWS Athena“で同じように”char_length“を使いたかったのだが、そんな関数はないとエラーになったので”length“を使用して実行する事ができた。

mysql> help length
Name: 'LENGTH'
Description:
Syntax:
LENGTH(str)

Returns the length of the string str, measured in bytes. A multi-byte
character counts as multiple bytes. This means that for a string
containing five two-byte characters, LENGTH() returns 10, whereas
CHAR_LENGTH() returns 5.
  :
mysql>
mysql> help char_length
Name: 'CHAR_LENGTH'
Description:
Syntax:
CHAR_LENGTH(str)

Returns the length of the string str, measured in characters. A
multi-byte character counts as a single character. This means that for
a string containing five two-byte characters, LENGTH() returns 10,
whereas CHAR_LENGTH() returns 5.
  :
mysql>

以上。

unixtimeをSQLで変換

MySQLにおいて表題の通り。

■ 環境

  • MySQL 5.6.35

■ from_unixtime()

MySQLのテーブルにエポック秒で保存されているカラムがある。これを人が理解しやすい時間に変換したい。

mysql> SELECT FROM_UNIXTIME(0);
+---------------------+
| from_unixtime(0)    |
+---------------------+
| 1970-01-01 00:00:00 |
+---------------------+
1 row in set (0.01 sec)

mysql&gtl

これで変換できた。便利だ。

mysql> select from_unixtime(1504034383);
+---------------------------+
| from_unixtime(1504034383) |
+---------------------------+
| 2017-08-29 19:19:43       |
+---------------------------+
1 row in set (0.00 sec)

mysql>

以上。

■ 関連

dateでunixtimeを文字列に変換

datetime型をunixtimeに変換

version@MySQL Server

表題の通り。ふとMySQLのサーバ側のversionっていくつだっけ?と思ったのでメモ。

■ 環境

  • MySQL 5.6.35
  • Amazon Linux

■ mysql

`mysql`コマンドで接続した時に表示される下記の出力の中にversion情報が含まれている。

$ mysql -u withsin -p -h ........
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 74142
Server version: 5.6.35-log MySQL Community Server (GPL)
  :

ここにも表示されているのだが、コマンドで知るにはどうしたら良いのか。`mysqladmin`コマンドでも取得できるようだが、それはそれで面倒である。

手っ取り早く現在接続中のコネクションの中で終えたい。下記のようにできるようである。

mysql> status
--------------
mysql  Ver 14.14 Distrib 5.6.36, for Linux (x86_64) using  EditLine wrapper

Connection id:        74817
Current database:    withsin
Current user:        withsin@XXX.XXX.XXX.XXX
SSL:            Not in use
Current pager:        stdout
Using outfile:        ''
Using delimiter:    ;
Server version:        5.6.35-log MySQL Community Server (GPL)
Protocol version:    10
Connection:        withsin.XXXXXXXXXXX.ap-northeast-1.rds.amazonaws.com via TCP/IP
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    utf8
TCP port:        3306
Uptime:            14 hours 21 sec

Threads: 23  Questions: 4947365  Slow queries: 0  Opens: 1504  Flush tables: 1  Open tables: 1419  Queries per second avg: 114.466
--------------

mysql>

最初に表記されている”5.6.36“はクライアント側のversionである。サーバ側のversionは”5.6.35“である。

以上。

■ 関連

MySQL status