Postgresql将十六进制转换为时间戳（从SQL Server迁移时） [英] Postgresql cast hexadecimal to timestamp (when migrating from SQL Server)

问题描述

解析SQL Server对PostgresSQL的插入查询时遇到以下问题。在对查询进行必要的更改以使SQL语句正常工作之后，我在使用 CAST（）函数时遇到了一些麻烦。

I have the following problem when parsing SQL Server insert queries to PostgresSQL. After making the necessary changes to the queries in order for the SQL statements to work, I have been having some troubles with the CAST() function.

CAST(x00009DD500000000 AS timestamp)

1. 如果我写 CAST（0x000 ....），pgAdmin表示x附近存在语法错误

1. If I write CAST(0x000....), pgAdmin says that there is a syntax error near x

如果删除0，则会出现该列不存在的错误

If I remove the 0, I get the error that the column does not exist

我当时以为列数可能是一个错误，但是由于我要处理大量数据，因此我无法看到将十六进制转换为时间戳的函数PostgreSQL，我想知道这是否可能导致其他错误。

I was thinking it could be an error with the amount of columns, but since I am working with a lot of data and I haven't been able to see a function which converts a hexadecimal to timestamp in postgresql, I wanted to know if that could be causing some other kind of error. Maybe?

在Postgresql中是否有将十六进制值转换为时间戳的函数？

Is there a function to convert an hexadecimal value to timestamp in Postgresql?

因为转换了十六进制将值添加到另一种类型的数据将花费大量时间。

Because converting the hexadecimal value to another type of data would take to much time.

预先感谢！

推荐答案

此时间戳记符号特定于MSSQL。它分为两个部分：

This timestamp notation is specific for MSSQL. It has two parts:

• 日期部分-自1900-01-01以来的天数


• 时间部分-精度为1 / 300s

在PostgreSQL中没有自动强制转换此值。
确切的解决方案，您可以在这里找到：
如何投射十六进制为varchar（datetime）？

There is no automatic cast for this value in PostgreSQL. Exact solution you can find here: how to cast the hexadecimal to varchar(datetime)?

这篇关于Postgresql将十六进制转换为时间戳（从SQL Server迁移时）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！