首页 \ 问答 \ 在执行查询之前,在SQLAlchemy模型中将datetime转换为unix时间戳?(Convert datetime to unix timestamp in SQLAlchemy model before executing query?)

在执行查询之前,在SQLAlchemy模型中将datetime转换为unix时间戳?(Convert datetime to unix timestamp in SQLAlchemy model before executing query?)

我正在使用SQLAlchemy来处理使用奇怪时间戳格式的远程数据库 - 它将时间戳存储为自纪元以来的双精度毫秒。 我想使用python datetime对象,所以我在我的模型中编写了getter / setter方法,遵循以下要点

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import synonym
from sqlalchemy.dialects.mysql import DOUBLE
import datetime

Base = declarative_base()
class Table(Base):
    __tablename__ = "table"

    id = Column(Integer, primary_key=True)
    _timestamp = Column("timestamp", DOUBLE(asdecimal=False))

    @property
    def timestamp(self):
        return datetime.datetime.utcfromtimestamp(float(self._timestamp)/1000.)

    @timestamp.setter
    def timestamp(self, dt):
        self._timestamp = float(dt.strftime("%s"))*1000.

    timestamp = synonym('_timestamp', descriptor=timestamp)

这非常适合在表中插入新行并使用表中的对象:

>>> table = session.query(Table).first()
<Table id=1>
>>> table.timestamp
datetime.datetime(2016, 6, 27, 16, 9, 3, 320000)
>>> table._timestamp
1467043743320.0

但是,当我尝试在过滤器表达式中使用datetime时,它会崩溃:

>>> july = datetime.datetime(2016, 7, 1)
>>> old = session.query(Table).filter(Table.timestamp < july).first()
/lib/python2.7/site-packages/sqlalchemy/engine/default.py:450: Warning: Truncated incorrect DOUBLE value: '2016-07-01 00:00:00'
>>> july_flt = float(july.strftime("%s"))*1000.
>>> old = session.query(Table).filter(Table.timestamp < july_flt).first()
<Table id=1>

我假设这是因为我的getter / setter方法适用于表类的实例,但不改变类本身的行为。 我尝试使用混合属性而不是同义词进行重写:

from sqlalchemy.ext.hybrid import hybrid_property

class Table(Base):
    __tablename__ = "table"

    id = Column(Integer, primary_key=True)
    _timestamp = Column("timestamp", DOUBLE(asdecimal=False))

    @hybrid_property
    def timestamp(self):
        return datetime.datetime.utcfromtimestamp(float(self._timestamp)/1000.)

    @timestamp.setter
    def timestamp(self, dt):
        self._timestamp = float(dt.strftime("%s"))*1000.

同样,这适用于Table实例,但在查询上失败 - 现在它在我运行查询时遇到了我的getter方法:

>>> july = datetime.datetime(2016, 7, 1)
>>> old = session.query(Table).filter(Table.timestamp < july).first()
Traceback:
  File "models.py", line 42, in timestamp
    return datetime.datetime.utcfromtimestamp(float(self._timestamp)/1000.)
TypeError: float() argument must be a string or a number

使用调试器,我可以看到getter正在接收Table._timestamp类(不是特定的Table._timestamp,而不是'7月')。

我看到我可以使用hybrid_property.expression装饰器来定义用于将时间戳转换为datetime的SQL表达式,但我真正喜欢的是将日期时间转换为python端的时间戳,然后使用时间戳运行查询。 换句话说,我想在任何地方使用日期时间(包括在查询中),但是在SQL端使用微秒时间戳完成所有操作。 我怎样才能做到这一点?


I am using SQLAlchemy to work with a remote database that uses a strange timestamp format--it stores timestamps as double-precision milliseconds since epoch. I'd like to work with python datetime objects, so I wrote getter/setter methods in my model, following this gist:

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import synonym
from sqlalchemy.dialects.mysql import DOUBLE
import datetime

Base = declarative_base()
class Table(Base):
    __tablename__ = "table"

    id = Column(Integer, primary_key=True)
    _timestamp = Column("timestamp", DOUBLE(asdecimal=False))

    @property
    def timestamp(self):
        return datetime.datetime.utcfromtimestamp(float(self._timestamp)/1000.)

    @timestamp.setter
    def timestamp(self, dt):
        self._timestamp = float(dt.strftime("%s"))*1000.

    timestamp = synonym('_timestamp', descriptor=timestamp)

This works great for inserting new rows into the table and working with objects from the table:

>>> table = session.query(Table).first()
<Table id=1>
>>> table.timestamp
datetime.datetime(2016, 6, 27, 16, 9, 3, 320000)
>>> table._timestamp
1467043743320.0

However, it breaks down when I try to use a datetime in a filter expression:

>>> july = datetime.datetime(2016, 7, 1)
>>> old = session.query(Table).filter(Table.timestamp < july).first()
/lib/python2.7/site-packages/sqlalchemy/engine/default.py:450: Warning: Truncated incorrect DOUBLE value: '2016-07-01 00:00:00'
>>> july_flt = float(july.strftime("%s"))*1000.
>>> old = session.query(Table).filter(Table.timestamp < july_flt).first()
<Table id=1>

I assume this is because my getter/setter methods apply to instances of the table class, but don't change the behavior of the class itself. I've tried rewriting using a hybrid property instead of a synonym:

from sqlalchemy.ext.hybrid import hybrid_property

class Table(Base):
    __tablename__ = "table"

    id = Column(Integer, primary_key=True)
    _timestamp = Column("timestamp", DOUBLE(asdecimal=False))

    @hybrid_property
    def timestamp(self):
        return datetime.datetime.utcfromtimestamp(float(self._timestamp)/1000.)

    @timestamp.setter
    def timestamp(self, dt):
        self._timestamp = float(dt.strftime("%s"))*1000.

Again, this works with Table instances, but fails on a query--now it's hitting my getter method when I run the query:

>>> july = datetime.datetime(2016, 7, 1)
>>> old = session.query(Table).filter(Table.timestamp < july).first()
Traceback:
  File "models.py", line 42, in timestamp
    return datetime.datetime.utcfromtimestamp(float(self._timestamp)/1000.)
TypeError: float() argument must be a string or a number

With the debugger, I can see that the getter is receiving the Table._timestamp class (not a specific Table._timestamp, and not 'july').

I see that I could use the hybrid_property.expression decorator to define a SQL expression for converting timestamps into datetime, but what I'd really like is to convert the datetime into a timestamp on the python side, then run the query using timestamps. In other words, I'd like to use datetimes everywhere (including in queries), but have everything done with the microsecond timestamps on the SQL side. How can I do this?


原文:https://stackoverflow.com/questions/38357352
更新时间:2020-05-09 07:48

相关问答

更多

MySQL将datetime转换为Unix时间戳(MySQL convert date string to Unix timestamp)

尝试CONVERT DATETIME to UNIX TIME STAMP此查询 SELECT UNIX_TIMESTAMP(STR_TO_DATE('Apr 15 2012 12:00AM', '%M %d %Y %h:%i%p')) 此查询CHANGE DATE FORMATE SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(STR_TO_DATE('Apr 15 2012 12:00AM', '%M %d %Y %h:%i%p')),'%m-%d-%Y %h:%i

使用mysql unix时间戳定义一个包含sqlalchemy的表(Defining a table with sqlalchemy with a mysql unix timestamp)

我认为你展示的类型装饰器有几个问题。 impl应该是sqlalchemy.types.Integer而不是DateTime 。 装饰者应该允许可为空的列。 以下是我的想法: import datetime, time from sqlalchemy.types import TypeDecorator, DateTime, Integer class IntegerDateTime(TypeDecorator): """a type that decorates DateTime,

在python中将datetime转换为unix时间戳(Convert datetime to unix timestamp in python)

from datetime import datetime from datetime import timedelta from calendar import timegm utc_dt = datetime.utcfromtimestamp(self.__modified_time) from_zone = tz.tzutc() to_zone = tz.tzlocal() utc = utc_dt.replace(tzinfo=from_zone) central = utc.ast

PHP将MySQL datetime转换为Unix时间戳(PHP convert MySQL datetime to Unix Timestamp)

strtotime函数将假定datetime在服务器的本地时区。 The strtotime function will assume the datetime is in the server's local timezone.

SQLAlchemy最接近的日期时间(SQLAlchemy nearest datetime)

由于它是主键,简单的“按差异排序,获取第一行”可能不是最快的,虽然简单的解决方案。 快速而肮脏的方法可能是获取大于和小于给定日期时间的并集,按升序和降序排序并限制为第1行,然后从2中选择具有较小diff的结合。 这是一个使用Postgresql作为后端的示例和一个具有12年分辨率的一年的时间戳的测试表: sopython=> create table testi (key timestamp without time zone primary key); CREATE TABLE sopytho

将unix时期时间戳转换为TSQL日期时间(Convert unix epoch timestamp to TSQL datetime)

很简单,首先添加整天,然后添加剩余的ms。 一天有86,400,000毫秒。 declare @unixTS bigint set @unixTS = 1359016610667 select dateadd(ms, @unixTS%(3600*24*1000), dateadd(day, @unixTS/(3600*24*1000), '1970-01-01 00:00:00.0') ) 结果是2013-01-24 08:36:50.667 Easy, first add wh

带参数的UNIX_TIMESTAMP()是否会查询缓存?(Will UNIX_TIMESTAMP() with parameter break query cache?)

如果将值或列作为参数传递给UNIX_TIMESTAMP()它仍将被缓存。 如果您不知道,也值得了解,对表的任何更改都会导致缓存被清除。 因此,如果您要查询的表经常更改,您可能没有太多查询缓存的好处。 从5.5 手册 : 如果查询包含下表中显示的任何函数,则无法缓存该查询。 - 没有参数的UNIX_TIMESTAMP() It will still be cached if you pass a value or column as a parameter to UNIX_TIMESTAMP().

将ticks转换为unix时间戳(Convert ticks to unix timestamp)

C#ticks是自0001-01-01 00:00:00午夜以来的一些滴答(每个滴答是1/10000000秒),UNIX时间戳是自UNIX纪元开始以来的秒数( 1970-01-01 01:00:00 ),所以期望的结果是$seconds = 634942626000000000/10000000 - $number_of_seconds ,其中$number_of_seconds是0001-00-01 00:00:00和1970-01-01 01:00:00 0001-00-01 00:00:0

相关文章

更多

最新问答

更多
  • jsPlumb draggable element javascript函数(jsPlumb draggable element javascript function)
  • MVC4:ViewModel(带有radiobuttonlist)在HttpPost之后为空(MVC4: ViewModel (with radiobuttonlist) is empty after HttpPost)
  • 如何在同一帐户上设置“Dev repo”(在prod和团队之间)(How to set up a “Dev repo” (between the prod and the team) on the same account)
  • 如何在tcl中将eth0配置为发送方udp端口(how to configure eth0 as a sender udp port in tcl)
  • 如何在datarow []中的列中找到最大值?(How to find max value in a column in a datarow[] ?)
  • 如何使用预定义文本替换来自数据库的部分结果(How do I replace part of result coming from Database with predefined text)
  • Selenium Java注入了新的Javascript函数(Selenium Java inject new Javascript function)
  • 使用.on的多个下拉菜单选择文本仅适用于第一个下拉列表(Multiple Dropdowns Menu Selection text using .on works only on first dropdown)
  • 快速将黄土曲线添加到大型数据集图中的方法(Quick way to add loess curve to large data set graph)
  • FilteringSelect in mvc(FilteringSelect in mvc)
  • 在Delphi XE2中开发Mac或iOS应用程序需要哪些硬件/软件?(What hardware/software is necessary to develop Mac or iOS apps in Delphi XE2?)
  • 在原型的构造函数中初始化属性时获取“未定义”(Getting 'undefined' when a property is initialized in the constructor of a prototype)
  • 通过越狱加载的应用程序的Documents文件夹位置(Location of Documents folder for an app loaded via jailbreak)
  • 在OpenGL中使用可编程和固定管道功能(Using both programmable and fixed pipeline functionality in OpenGL)
  • 将任何用户输入重定向到单独的底层程序(redirect any user input to a separate underlying program)
  • 编辑文本不能正常工作android(Edit texts not working properly android)
  • “user_denied”Facebook应用页面上的Facebook用户区域设置(Facebook user locale on “user_denied” facebook app page)
  • 在大图像中找到小的部分透明图像的坐标(find coordinates of small partially-transparent image within a large image)
  • 我如何在cakephp 3.1中获得完整的相对路径?(How i can get full relative path of image in cakephp 3.1?)
  • 如何保存拖动标记的新本地化?(How to save new localization of dragged marker?)
  • MySQL UPDATE vs INSERT和DELETE(MySQL UPDATE vs INSERT and DELETE)
  • 在执行查询之前,在SQLAlchemy模型中将datetime转换为unix时间戳?(Convert datetime to unix timestamp in SQLAlchemy model before executing query?)
  • OpenCL与OpenGL互操作的优势(Advantage of OpenCL interoperability with OpenGL)
  • 如何解析用点和等分隔的数据然后添加到listview(How to parsing data from delimited with dot and equal then add to listview)
  • 带调试输出的X3解析器段错误(BOOST_SPIRIT_X3_DEBUG)(X3 parser segfaults with debug output (BOOST_SPIRIT_X3_DEBUG))
  • 将文件夹名称添加到fgrep结果(Add folder name to fgrep result)
  • 在MySQL中加载一个表是非常慢的(Loading one table in MySQL is ridiculously slow)
  • 如何将JSON放入PHP变量?(How do I put JSON into a PHP Variable?)
  • 如何绕过Microsoft.Speech.Recognition中的不流畅?(How to bypass disfluencies in Microsoft.Speech.Recognition?)
  • 原点的最后一行是什么?(What is the last row of an origin for?)