String type in the output meanwhile type is unsigned integer

[dbdeadka]

Hi, all!

May be my issue concerns not only lua-cjson module, may be it concernes another openresty lua-modules too ( f.e. mysql ), but it seems to me that "root" of the problem is in cjson.

I am using openresty.

root@machinename:/usr/local/openresty/nginx/sbin# ./nginx -V
nginx version: openresty/1.9.15.1
built by gcc 5.2.1 20151010 (Ubuntu 5.2.1-22ubuntu2)
built with OpenSSL 1.0.2d 9 Jul 2015 (running with OpenSSL 1.0.2g 1 Mar 2016)
TLS SNI support enabled
configure arguments: --prefix=/usr/local/openresty/nginx --with-cc-opt=-O2 --add-module=../ngx_devel_kit-0.3.0 --add-module=../echo-nginx-module-0.59 --add-module=../xss-nginx-module-0.05 --add-module=../ngx_coolkit-0.2rc3 --add-module=../set-misc-nginx-module-0.30 --add-module=../form-input-nginx-module-0.12 --add-module=../encrypted-session-nginx-module-0.05 --add-module=../drizzle-nginx-module-0.1.9 --add-module=../srcache-nginx-module-0.31 --add-module=../ngx_lua-0.10.5 --add-module=../ngx_lua_upstream-0.05 --add-module=../headers-more-nginx-module-0.30 --add-module=../array-var-nginx-module-0.05 --add-module=../memc-nginx-module-0.17 --add-module=../redis2-nginx-module-0.13 --add-module=../redis-nginx-module-0.3.7 --add-module=../rds-json-nginx-module-0.14 --add-module=../rds-csv-nginx-module-0.07 --with-ld-opt=-Wl,-rpath,/usr/local/lib:/usr/local/openresty/luajit/lib --with-pcre-jit --with-ipv6 --with-http_ssl_module

I have to extract some data from mysql-table (mysql-version: 5.6) and return to the user json-object with this data.

mysql> create table cjsontest(id int primary key auto_increment, ts timestamp not null default now());
Query OK, 0 rows affected (0.01 sec)
mysql> insert into cjsontest() values(),();
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from cjsontest;
+----+---------------------+
| id | ts |
+----+---------------------+
| 1 | 2017-10-10 20:55:34 |
| 2 | 2017-10-10 20:55:34 |
+----+---------------------+
2 rows in set (0.00 sec)

I have to get timestamp (integer), but cjson module returns the string type of this (meanwhile
I am using unix_timestamp function and in the documentation https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_unix-timestamp mentioned returned type
as an unsigned integer
).

My lua-code is below:

content_by_lua_block {

            mysql = require "resty.mysql"

            cjson = require "cjson"

            local db = mysql:new()

            local ok, err, errno, sqlstate = db:connect(config.database)

            if not ok then

                    ngx.log(ngx.ERR, "failed to connect: ", err, ": ", errno, " ", sqlstate)

                    return ngx.exit(ngx.HTTP_INTERNAL_SERVER_ERROR)

            end

            local sqlquery = [[SELECT
    id
    ,ts ts1
    ,UNIX_TIMESTAMP(ts) ts2
    ,CAST(UNIX_TIMESTAMP(ts) as UNSIGNED INTEGER) ts3
    ,CONVERT(UNIX_TIMESTAMP(ts), UNSIGNED INTEGER) ts4 FROM cjsontest
            ]]

            local res, err, errno, sqlstate = db:query(sqlquery)
            if not res then

                    local err = ": " .. err .. ": " .. errno .. ": " .. sqlstate .. ": " .. sqlquery .. "."
                    return ngx.exit(ngx.HTTP_INTERNAL_SERVER_ERROR)

            end

            ngx.header["Content-type"] = "application/json"

            ngx.say(cjson.encode(res))

            return ngx.exit(ngx.HTTP_OK)

    }

Result:
[{"ts2":"1507668934","id":1,"ts4":"1507668934","ts3":"1507668934","ts1":"2017-10-10 20:55:34"},{"ts2":"1507668934","id":2,"ts4":"1507668934","ts3":"1507668934","ts1":"2017-10-10 20:55:34"}]

So all my attemption to get timestamp with the integer type failed.
Meanwhile I absolutely don't want to iterate over table res and call toumber function for each element.

What is the reason of this behaviour and which way is the best for me in this situation?

Thank you so much for any ideas.

[leafo]

This is a very old issue. It's likely your database driver returns date objects as strings instead of numbers. You will have to convert it to a number if that's what you want.