Tip of the Week ¶
‘Extracting’ a Date from a TIMESTAMP ¶
CREATE TABLE user_login(name TEXT, login_time TIMESTAMP);
The TIMESTAMP type stores a complete date and time with or without timezone, as opposed to DATE or TIME which respectively store only those particular elements.
But what if you want to return only the date a TIMESTAMP refers to?
There are lots of date and time functions in Postgres, and you could extract the date elements piece by piece using EXTRACT:
SELECT EXTRACT(MONTH FROM TIMESTAMP '2020-09-21 12:21:13');
SELECT EXTRACT(DAY FROM TIMESTAMP '2020-09-21 12:21:13');
SELECT EXTRACT(YEAR FROM TIMESTAMP '2020-09-21 12:21:13');
But the easiest way is to cast the TIMESTAMP type into a DATE which automatically does the conversion needed:
SELECT name, login_time::date FROM user_login;
name | login_time
------+------------
john | 2019-11-11
bill | 2020-10-22
jane | 2020-04-01
(3 rows)
(Bill logged in from the future..?)
You could also use the DATE function to create a date in a similar way:
SELECT DATE('2020-09-21 12:21:13');
# => 2020-09-21T00:00:00.000Z
How to auto generate UUID columns when creating mapping classes with SQLAlchemy ¶
For Python developers using the SQLAlchemy ORM framework, you can let the database server auto-generate UUID columns when creating mapping classes.
This is valuable since it saves you from needing to use application libraries. Use the server_default parameter and call the gen_random_uuid() function:
class Account(Base):
"""The Account class corresponds to the "accounts" database table."""
__tablename__ = 'accounts'
id = Column(UUIDtype, server_default=text("gen_random_uuid()", primary_key=True)
balance = Column(Integer)
The Column.server_default parameter can be used to call any database function for setting a default value of a column.