convert datetime to unix timestamp in sqlalchemy model before executing query?

  • Last Update :
  • Techknowledgy :

You have to use a custom type, which isn't as scary as it sounds.

from sqlalchemy.types
import TypeDecorator

class DoubleTimestamp(TypeDecorator):
   impl = DOUBLE

def __init__(self):
   TypeDecorator.__init__(self, as_decimal = False)

def process_bind_param(self, value, dialect):
   return value.replace(tzinfo = datetime.timezone.utc).timestamp() * 1000

def process_result_value(self, value, dialect):
   return datetime.datetime.utcfromtimestamp(value / 1000)

Then Table becomes:

class Table(Base):
   __tablename__ = "table"

id = Column(Integer, primary_key = True)
timestamp = Column(DoubleTimestamp)

Suggestion : 2

Use the Appropriate Function to Obtain the Current Datetime,Function to Use Obtain the Current Instant,Obtain the Appropriate ODBC Driver,Most Appropriate Method to Define a Datetime Value

DATE '2018-01-15'
to_localdate('yyyy-MM-dd', '2018-01-15')
TIME '21:45:59'

TIME '21:45:59.999'
to_time('HH:mm:ss.SSS', '21:45:59.999')
TIMESTAMP '2018-01-15 21:45:01'

TIMESTAMP '2018-01-15 21:45:01.256'
   'yyyy-MM-dd HH:mm:ss.SSS', '2018-01-15 21:45:01.256')

Suggestion : 3

Last Updated : 25 Jan, 2022




USE sample_db;

We need to convert data from one data type to another, so first, we will select our data using the “SELECT command” in SQL. We will then pass the data that we have selected from our valuesDatetime column as a parameter to the DATEDIFF() function. The syntax of the DATEDIFF() function is : 

Syntax: DATEDIFF(part, start_datetime, end_datetime)
   part: This is the unit in which the difference between the Datetime objects is returned.
start_datetime: Initial Datetime object
end_datetime: Final Datetime object
Returns: Difference between start_datetime and end_datetime in the unit defined in part parameter.

Suggestion : 4

timezone — Timezone for the returned value. String. This argument is a constant, because toTimezone changes the timezone of a column (timezone is an attribute of DateTime* types).,Returns a timezone offset in seconds from UTC. The function takes into account daylight saving time and historical timezone changes at the specified date and time. IANA timezone database is used to calculate the offset.,Converts time or date and time to the specified time zone. The time zone is an attribute of the Date and DateTime data types. The internal value (number of seconds) of the table field or of the resultset's column does not change, the column's type changes and its string representation changes accordingly.,timezone — Timezone name for the returned value (optional). If not specified, the function uses the timezone of the value parameter. String.

SELECT toDateTime('2016-06-15 23:00:00') AS time, toDate(time) AS date_local, toDate(time, 'Asia/Yekaterinburg') AS date_yekat, toString(time, 'US/Samoa') AS time_samoa
time─┬─ date_local─┬─ date_yekat─┬─ time_samoa──────────┐│ 2016 - 06 - 15 23: 00: 002016 - 06 - 152016 - 06 - 162016 - 06 - 15 09: 00: 00│└─────────────────────┴────────────┴────────────┴─────────────────────┘
toTimezone(value, timezone)
SELECT toDateTime('2019-01-01 00:00:00', 'UTC') AS time_utc, toTypeName(time_utc) AS type_utc, toInt32(time_utc) AS int32utc, toTimeZone(time_utc, 'Asia/Yekaterinburg') AS time_yekat, toTypeName(time_yekat) AS type_yekat, toInt32(time_yekat) AS int32yekat, toTimeZone(time_utc, 'US/Samoa') AS time_samoa, toTypeName(time_samoa) AS type_samoa, toInt32(time_samoa) AS int32samoaFORMAT Vertical;
Row 1: ──────time_utc: 2019 - 01 - 01 00: 00: 00 type_utc: DateTime('UTC') int32utc: 1546300800 time_yekat: 2019 - 01 - 01 05: 00: 00 type_yekat: DateTime('Asia/Yekaterinburg') int32yekat: 1546300800 time_samoa: 2018 - 12 - 31 13: 00: 00 type_samoa: DateTime('US/Samoa') int32samoa: 1546300800