1.可以试试excel设置格式里选择时间格式，如果不行的话：
2.可以在查询时,将时间字段通过 date_format(日期字段,’%Y-%m-%d’) as ‘日期’ 的方式格式化一下, 然后,在EXCEL输出时, 干脆直接用字符串形式输出,好了.
如果还不行, 那你就讲windows的系统时间格式设置成 yyyy-MM-dd HH:mm:ss 吧.
mysql 导出数据日期格式
已采纳
用mysql导出数据，在查询时日期是yyyymmdd hh:mm:ss，但是导成xls格式，其中日期变成了mmddyyyy hh:mm:ss。这是什么原因？求解。
- 点赞
- 写回答
- 关注问题
- 收藏
- 复制链接分享
- 邀请回答
2条回答
-
采纳
点赞 1 评论 复制链接分享
-
采纳
导出的时候，用查询语句格式化一下日期格式
http://blog.csdn.net/embedded_sky/article/details/42890159点赞 1 评论 复制链接分享
提交
相关推荐
- 4年前回答 2 已采纳 用mysql导出数据，在查询时日期是yyyymmdd hh:mm:ss，但是导成xls格式，其中日期变成了mmddyyyy hh:mm:ss。这是什么原因？求解。
- 回答 1 已采纳 excel表内的时间导入到mysql datetime类型的数据是0000-00-00 00:00:00,该怎么处理才好 工具是navicat for mysql
- 回答 3 已采纳 查询所有 SELECT DATE_FORMAT(START_time,'%Y') time,COUNT(id) FROM cti_record_files GROUP BY time  条件查询 SELECT DATE_FORMAT(START_time,'%Y') time,COUNT(id) FROM cti_record_files WHERE DATE_FORMAT(emotional_time, '%Y') >= '2018' AND DATE_FORMAT(emotional_time, '%Y') <= '2020' GROUP BY time  这个条件只是对时间做出限制，但所有录音均为超出这个时间段
- 回答 4 已采纳 # 目的： **把MySQL某个表里面的数据取出来，传到Prometheus实现一个监控预警的作用，最后在Grafana生成监控图表**。 **1：首先这个表的数据结构是这样的（下面有图）**： 第一列（自增id）； 第二列（目录列表）； 第三列（已经使用的内存配额）； 第四列（总共的内存配额）； 第五列（插入这条数据的时间）。 **2：现在的想法是用python** （最好是python3）解析MySQL中表的数据，解析出来的数据传入Prometheus，然后实现一个对内存使用率超过多少后的预警，同时会有一个Grafana图表界面展示现在的内存使用状态。 **3：大概出来的样子：（例如使用超过80%有预警**）  **4：数据是这个样子：（数据是有按时间每小时在库里面刷新一次每个目录当前的用量）**  **5：目前想做的其实就是把MySQL表中的数据推到Prometheus 中做监控，展示也可以先不管，只要一个可以推送MySQL数据到Prometheus 的demo就行** GitHub上有个参考的，但是我不知道怎么弄，也可以告诉我这个怎么做：https://github.com/albertodonato/query-exporter 参考二：https://github.com/mr-karan/store-exporter
- 回答 2 已采纳 I cannot get mysql timestamp value in into a time.Time variable I am trying to scan a row, All values get successfully scanned except the timestamp type in mysql, I am already using dsn parseTime=true which was one of the issues i searched into, but it does not help type Tags struct { ID int CreatedAt time.Time `json:"created_at"` } func foo5() { http.HandleFunc("/tags/", bar5) http.ListenAndServe(":8080", nil) } func bar5(w http.ResponseWriter, r *http.Request) { db, err := sql.Open("mysql", "root:*******@/catalogue_service?parseTime=true") if err != nil { fmt.Println("error opening") } v, _ := strconv.Atoi(path.Base(r.URL.Path)) row := db.QueryRow("select * from tags where id = ?", v) var myRow Tags row.Scan(&myRow.ID, &myRow.CreatedAt) fmt.Println(myRow) } int and string values are displayed properly but CreatedAt prints 0001-01-01 00:00:00 +0000 UTC instead of the actual value Edit: +-----------------+--------------------------+------+-----+-------------------+-----------------------------------------------+ | Field | Type | Null | Key | Default | Extra | +-----------------+--------------------------+------+-----+-------------------+-----------------------------------------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | created_at | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED | +-----------------+--------------------------+------+-----+-------------------+-----------------------------------------------+ | 84 | 2019-01-30 11:21:36 |
- 4年前回答 2 已采纳 mysql 时间按日月年分组知道，但是如何按15分钟分组呢？
- 回答 3 已采纳 数据库的时间字段是年月日时分秒格式，通过springboot获取数据库信息，展示出来的是年月日时分格式 ，秒省略掉了。哪个环节能导致这个问题？
- 回答 2 已采纳 Recently I uninstalled the Wamp Server and re-installed the newer version of Wamp Server which is 3.1.3 Note: I am using Windows 10 Below are all the details of Wamp Server: Apache Version: 2.4.33 PHP Version: 5.6.35 MySQL Version: 5.7.21 phpMyAdmin Version: 4.7.9 When I click on MYSQL Database in phpMyAdmin and then click on Export, the phpMyAdmin shows blank page. However, when I click on Import, phpMyAdmin doesn't show blank page at all. But the strange thing is when clicking on Export, phpMyAdmin shows Blank Page. Since, I'm trying to export a Database from phpMyAdmin, I need to make sure that the page doesn't go blank when clicking on "Export" button. I'm not sure regarding how to approach this problem because I'm very new in phpMyAdmin. Would be really helpful if any recommended advice or solution is provided.
- 回答 1 已采纳 Problem: I want to run this query in cakephp 3 SELECT medicine_records.*, medicines.name AS medicine_name, medicines.subname AS `medicine_subname` FROM medicine_records INNER JOIN medicines ON medicines.id = medicine_records.medicine_id Controller: $medicines = $this->MedicineRecords->find()->contain(['Medicines'])->all(); Cakephp Queries log: 2017-02-14 09:58:47 Debug: duration=1 rows=1 SELECT MedicineRecords.id AS `MedicineRecords__id`, MedicineRecords.user_id AS `MedicineRecords__user_id`, MedicineRecords.medicine_id AS `MedicineRecords__medicine_id`, MedicineRecords.times_pre_day AS `MedicineRecords__times_pre_day`, MedicineRecords.created AS `MedicineRecords__created`, MedicineRecords.modified AS `MedicineRecords__modified`, Medicines.id AS `Medicines__id`, Medicines.name AS `Medicines__name`, Medicines.subname AS `Medicines__subname`, Medicines.type AS `Medicines__type`, Medicines.created AS `Medicines__created`, Medicines.modified AS `Medicines__modified` FROM medicine_records MedicineRecords INNER JOIN medicines Medicines ON Medicines.id = (MedicineRecords.medicine_id) Result data: "data": [ { "id": 1, "user_id": 1, "medicine_id": 3, "times_pre_day": 2, "created": "2017-02-14T04:55:48+00:00", "modified": "2017-02-14T04:55:48+00:00", "medicine": { "id": 3, "name": "Name", "subname": "Subname", "type": 1, "created": "2017-02-13T09:38:48+00:00", "modified": "2017-02-13T09:38:48+00:00" } } ] This result has medicine in child level. I want all data in top level like this: "data": [ { "id": 1, "user_id": 1, "medicine_id": 3, "times_pre_day": 2, "medicine_name": "Name", "medicine_subname": "Subname", "created": "2017-02-14T04:55:48+00:00", "modified": "2017-02-14T04:55:48+00:00", } ] I already try to use public $recursive = -1; but I think it not working on Cakephp 3. Update Remove WHERE medicine_records.id = 1
- 回答 3 已采纳 I'm trying to update 2 tables tbl_loanledger and tbl_journal at the same time. Both tables have the same columns such as modified_by, date_modified and deleted. When I try to run this I enter code here I'm getting an error : Error Number: 1052 Column 'modified_by' in field list is ambiguous UPDATE `loan_ledger` as a, journal as b SET `modified_by` = '1', `date_modified` = '2016-06-08', `deleted` = 1 WHERE `a`.`id` = '823' AND `b`.`id` = '823' Filename: C:\xampp\htdocs\system\system\database\DB_driver.php Line Number: 331 Is this the right way on how to update 2 tables in codeigniter? Model public function delete($id){ $data = array( 'modified_by' => $this->ion_auth->user()->row()->id, 'date_modified' => date("Y-m-d"), 'deleted' => 1 ); $this->db->set($data); $this->db->where('a.id', $id); $this->db->where('b.id', $id); $this->db->update('loan_ledger as a, journal as b'); } But if I am just updating single table, it is working, here is a the code: public function delete($id){ $data = array( 'modified_by' => $this->ion_auth->user()->row()->id, 'date_modified' => date("Y-m-d"), 'deleted' => 1 ); $this->db->where('id', $id); $this->db->update(tbl_loanledger, $data); }
- 文韬菌的博客 第二个问题是，outfile的路径一定要有写权限，我们mysql的进程权限一般是mysql用户，因此最好导出到/tmp目录下面。 2. 通过结合sed 复制代码 代码如下: mysql -uroot test -e "select ip from server where a.name ...
- weixin_39611722的博客 MySQL中如何只用SQL语句导出带日期格式的文件,导出文件使用SELECT INTO OUTFILE 但是OUTFILE后面的值不能使用变量，所以只能使用动态SQL语句来实现。其中表user为测试表。具体语句如下所示mysql> SET @SqlScript=...
- 陈同学在路上的博客 一、 select * from black_list1 into outfile "D:/...mysql> select * from black_list1 into outfile "D:/1234.txt";Query OK, 790913 rows affected (4.49 sec)结果：mysql> select * from black_list1 ...
- 刘宏甲的博客 假定日期为varchar数据类型，即不是日期格式。让我们首先创建一个表。以下是查询-mysql>createtableconvertDateDemo->(->AdmissionDatevarchar(200)->);以下是使用insert命令在表中插入一些记录的查询-...
- weixin_29200485的博客 [数据库]MySQL如何导出带日期格式的文件0 2015-12-30 21:00:08一网友问在MySQL中如何只用SQL语句导出带日期格式的文件。觉得有点意思，于是尝试了一下。导出文件使用SELECT INTO OUTFILE 但是OUTFILE后面的值不能...
- weixin_39600366的博客 Mysql自带数据导出的命令intooutfile(本地导出)SELECT * FROM server_warning_repairedinto outfile '/tmp/test.csv'CHARACTER SET gbkfields terminated by ',' optionally enclosed by '\"';整体来看，select语句+...
- 以花的姿势凋零的博客 最近几次导出Mysql数据的时候发现我的日期格式是月日年的，明明数据库里面是年月日。然后我以为在excel表格里面可以转为相应的年月日的，啊啊啊啊，崩溃了几次，都不能一次性的用快捷键转换。后来由于着急，就先手动...
- CPA研习社的博客 MySQL中你可以使用SELECT...INTO OUTFILE语句来简单的导出数据到文本文件上。使用 SELECT ... INTO OUTFILE 语句导出数据以下实例中我们将数据表 cnblogs_tbl 数据导出到 /tmp/tutorials.txt 文件中:mysql>SELECT...
- HUGOPOISSON的博客 www.002pc.com认为此文章对《mysql删除部分数据Sqoop导出MySQL数据》说的很在理，www.002pc.com为你提供最佳的mysql语法，mysql技术。导入所有表：[code]sqoop import-all-tables –connect jdbc:mysql://ip:port/...
- weixin_39717825的博客 使用python3实现mysql导出数据到文件的脚本如下：import pymysqlimport timehost = ''port =3306user =''passwd =''db =''sql = 'select ACCEPT_NO,BSS_ACT_CODE,USER_NO,ACC_NBR,PROD_CODE,PROD_NAME,COMBO_FEE,...