如何使用SQL从MongoDB ObjectId中检索日期 [英] How to retrieve the date from a MongoDB ObjectId using SQL

问题描述

在MongoDB中，您可以使用getTimestamp()函数从ObjectId中检索日期.如何使用SQL从MongoDB ObjectId中检索日期(例如，在将ObjectId存储在MySQL数据库中的情况下)?

In MongoDB you can retrieve the date from an ObjectId using the getTimestamp() function. How can I retrieve the date from a MongoDB ObjectId using SQL (e.g., in the case where such an ObjectId is stored in a MySQL database)?

示例输入:

507c7f79bcf86cd7994f6c0e

想要的输出:

2012-10-15T21:26:17Z

推荐答案

这可以通过以下方式实现(假设objectId是字符串):

This can be achieved as follows (assuming objectId is a string):

SELECT FROM_UNIXTIME(CAST(CONV(SUBSTR(objectId, 1, 8), 16, 10) AS UNSIGNED)) FROM table

它的工作原理如下:

• SUBSTR(objectId, 1, 8)从十六进制objectId字符串中获取前8个字符
• CONV(..., 16, 10)将十六进制数字转换为十进制数字，并将其作为字符串返回(代表UNIX时间戳)
• CAST (...) AS UNSIGNED将时间戳字符串转换为无符号整数
• FROM_UNIXTIME(...)将时间戳整数转换为日期

• SUBSTR(objectId, 1, 8) takes the first 8 characters from the hexadecimal objectId string
• CONV(..., 16, 10) converts the hexadecimal number into a decimal number and returns it as a string (which represents the UNIX timestamp)
• CAST (...) AS UNSIGNED converts the timestamp string to an unsigned integer
• FROM_UNIXTIME(...) converts the timestamp integer into the date

请注意，默认情况下，显示的日期将基于系统的时区设置.

Note that by default the displayed date will be based on your system's timezone settings.

这篇关于如何使用SQL从MongoDB ObjectId中检索日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！