Mysql 2023/03/05 12:23:00转 datetime

可以使用 MySQL 的 STR_TO_DATE 函数将格式为 '%Y/%m/%d %H:%i:%s' 的字符串转换为 DATETIME 类型，具体语法如下：

SELECT STR_TO_DATE('2023/03/05 12:23:00', '%Y/%m/%d %H:%i:%s');

这将返回一个 DATETIME 类型的值 '2023-03-05 12:23:00'。

相关问题

sqoop import --connect jdbc:mysql://zhaosai:3306/mydb --username root --password jqe6b6 --table news --target-dir /user/news --fields-terminated-by “;” --hive-import --hive-table news -m 1出现错误Warning: /opt/programs/sqoop-1.4.7.bin__hadoop-2.6.0/../hbase does not exist! HBase imports will fail. Please set $HBASE_HOME to the root of your HBase installation. Warning: /opt/programs/sqoop-1.4.7.bin__hadoop-2.6.0/../hcatalog does not exist! HCatalog jobs will fail. Please set $HCAT_HOME to the root of your HCatalog installation. Warning: /opt/programs/sqoop-1.4.7.bin__hadoop-2.6.0/../accumulo does not exist! Accumulo imports will fail. Please set $ACCUMULO_HOME to the root of your Accumulo installation. Warning: /opt/programs/sqoop-1.4.7.bin__hadoop-2.6.0/../zookeeper does not exist! Accumulo imports will fail. Please set $ZOOKEEPER_HOME to the root of your Zookeeper installation. 23/06/10 16:18:23 INFO sqoop.Sqoop: Running Sqoop version: 1.4.7 23/06/10 16:18:23 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead. 23/06/10 16:18:23 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset. 23/06/10 16:18:23 INFO tool.CodeGenTool: Beginning code generation Sat Jun 10 16:18:23 CST 2023 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification. 23/06/10 16:18:24 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM news AS t LIMIT 1 23/06/10 16:18:24 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM news AS t LIMIT 1 23/06/10 16:18:24 INFO orm.CompilationManager: HADOOP_MAPRED_HOME is /opt/programs/hadoop-2.7.6 注: /tmp/sqoop-root/compile/84ba419f00fa83cb5d16dba722729d01/news.java使用或覆盖了已过时的 API。 注: 有关详细信息, 请使用 -Xlint:deprecation 重新编译。 23/06/10 16:18:25 INFO orm.CompilationManager: Writing jar file: /tmp/sqoop-root/compile/84ba419f00fa83cb5d16dba722729d01/news.jar 23/06/10 16:18:25 WARN manager.MySQLManager: It looks like you are importing from mysql. 23/06/10 16:18:25 WARN manager.MySQLManager: This transfer can be faster! Use the --direct 23/06/10 16:18:25 WARN manager.MySQLManager: option to exercise a MySQL-specific fast path. 23/06/10 16:18:25 INFO manager.MySQLManager: Setting zero DATETIME behavior to convertToNull (mysql) 23/06/10 16:18:25 ERROR tool.ImportTool: Import failed: No primary key could be found for table news. Please specify one with --split-by or perform a sequential import with '-m 1'.

这段代码是用 Sqoop 工具从 MySQL 数据库中导入数据到 Hadoop 分布式文件系统，并在 Hive 中创建表。但是在导入过程中出现了错误，错误提示为“Import failed: No primary key could be found for table news. Please specify one with --split-by or perform a sequential import with '-m 1'.”，意思是在导入表 news 的过程中没有找到主键，需要使用 --split-by 参数指定一个主键或者使用 -m 1 进行串行导入。

有 2023-02-28 14:50:59 2023-02-28 14:50:59 2023-02-28 14:51:45 2023-02-28 14:51:45 2023-02-28 14:52:31 2023-02-28 14:52:31 2023-02-28 14:55:40 2023-02-28 14:55:40 2023-03-01 15:06:32 2023-03-01 15:06:32 2023-03-02 10:00:57 2023-03-02 10:00:57 2023-03-03 13:57:10 2023-03-03 13:57:10 2023-03-04 08:58:11 2023-03-04 08:58:11 2023-03-05 09:08:32 2023-03-05 09:08:32 2023-03-06 09:59:45 2023-03-06 09:59:45这么一组数据，要求用mysql查询，只取每天的最大值

可以使用MySQL中的DATE函数和MAX函数来实现：

SELECT DATE(datetime_column), MAX(time_column)
FROM table_name
GROUP BY DATE(datetime_column);

其中，`datetime_column` 为存储日期和时间的列名，`time_column` 为需要取最大值的列名，`table_name` 为表名。这条语句将会返回每天的最大值。