superset数据集birth_names的负时间戳处理

处理负时间戳

superset有个数据集叫birth_names.json

里面的ds这一栏全是负的

这个数据集的时间范围其实是:

1965-01-01 08:00:00~2008-01-01 08:00:00

下面的方案来自[2],但是有个要命的问题就是无法处理负时间戳

mysql> create table foo(id INT, mytimestamp INT(11)); Query OK, 0 rows affected (0.02 sec) Insert some valuesmysql> insert into foo values(1, 1381262848); Query OK, 1 row affected (0.01 sec) Take a lookmysql> select * from foo; +------+-------------+ | id | mytimestamp | +------+-------------+ | 1 | 1381262848 | +------+-------------+ 1 row in set (0.00 sec) Convert the number to a timestamp:mysql> select id, from_unixtime(mytimestamp) from foo; +------+----------------------------+ | id | from_unixtime(mytimestamp) | +------+----------------------------+ | 1 | 2013-10-08 16:07:28 | +------+----------------------------+ 1 row in set (0.00 sec) Convert it into a readable format:mysql> select id, from_unixtime(mytimestamp, '%Y %D %M %H:%i:%s') from foo; +------+-------------------------------------------------+ | id | from_unixtime(mytimestamp, '%Y %D %M %H:%i:%s') | +------+-------------------------------------------------+ | 1 | 2013 8th October 04:07:28 | +------+-------------------------------------------------+ 1 row in set (0.00 sec)

 

 

苦思冥想，最后将[3]中的命令修改如下:

SELECT DATE_ADD('1970-01-01 00:00:00',INTERVAL ds/1000 SECOND) from birth_names;

如果想要全部展示，那么:

SELECT DATE_ADD('1970-01-01 00:00:00',INTERVAL ds/1000 SECOND), gender,name,num ,state,sum_boys,sum_girls from birth_names;

 

处理后建立新的表格

如果想把查询结果另外新建一张表格，那么:

①导出为csv(注意不要修改导出路径)

SELECT DATE_ADD('1970-01-01 00:00:00',INTERVAL ds/1000 SECOND), gender,name,num ,state,sum_boys,sum_girls from birth_names into outfile '/var/lib/mysql-files/birth_names.csv' fields terminated by ',' optionally enclosed by '"' lines terminated by '\n';

导出之后发现日期格式变成这样了：

不用担心，因为如果用sublime打开的话，依然和查询结果一致

②csv通过datagrip导入mysql

先把老的表格删除:

drop table birth_names;

然后新建个表格:

create table birth_names (ds datetime null,gender text null,name text null,num int null,state text null,sum_boys int null,sum_girls int null );

 

右键表格->Import Data from file:

 

最终效果如下:

 

 

 

Reference:

[1]https://tool.lu/timestamp/

[2]Convert timestamp to date in MySQL query

[3]Converting negative values from FROM_UNIXTIME

总结

以上是生活随笔为你收集整理的superset数据集birth_names的负时间戳处理的全部内容，希望文章能够帮你解决所遇到的问题。

如果觉得生活随笔网站内容还不错，欢迎将生活随笔推荐给好友。