Best data type for PostgreSQL datatime type

postgresql datetime data type
postgres insert timestamp with timezone
postgres data types
postgres date query
postgres timezone
postgres date format dd/mm/yyyy
postgres create table timestamp with timezone
timestamp without timezone postgres

Actually I have:

CREATE TABLE public.user(
  id BIGSERIAL NOT NULL,
  nick VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL,
  born DATE,
  joined DATE,
  tags text[]
);

I want to choose best option to save register time, and born data. I searched internet but didnt find any good results. Maybe its better to use time from python, and just insert it to postgres?

Documentation: 9.2: Data Types, In this case TIMESTAMP would be more appropriate. Avoid using VARCHAR to store dates. Since it doesn't validate anything, it will allow you to  Date data type for dates that it doesn't require specific moment in a time like birthday date, published post (on a blog). Timestamp for specific moments like register in an app, managing login apps, transactional records, etc. Never merge code for both parts, so whether you decide manage database from Python code, you should use only Python code.

If you just want to store the plain time without timezone simply use timestamp probaböy useful for creation time or so on. And for columns like birthdate, just use Date

Best data type for PostgreSQL datatime type, PostgreSQL implementation of the calendar is very good, and we're going to show some mice example about how confusing this matter is. The  PostgreSQL has a rich set of native data types available to users. Users can add new types to PostgreSQL using the CREATE TYPE command. Table 8-1 shows all the built-in general-purpose data types. Most of the alternative names listed in the "Aliases" column are the names used internally by PostgreSQL

First of all, you need define how often you will change your database structure (adding or removing recordes). I would recommend you use an ORM for this boilerplate task like SQLAlchemy or PonyORM and avoiding manipulate database directly.

Answering your question: Whether you want add timestamp to any field, a trigger often would be useful for this task.

You always use:

Date data type for dates that it doesn't require specific moment in a time like birthday date, published post (on a blog).

Timestamp for specific moments like register in an app, managing login apps, transactional records, etc.

Never merge code for both parts, so whether you decide manage database from Python code, you should use only Python code.

In same sense, whether you decide manage tables and action on database, only use triggers and objects from database for this task.

I hope your question was answered!

PostgreSQL Data Types: Date, Timestamp, and Time Zones, This tutorial helps you understand the PostgreSQL timestamp data types: timestamp best practice to use the timestamptz data type to store the timestamp data. Summary: in this tutorial, we will introduce you to the PostgreSQL TIME data types and show you some handy functions to handle time values. Introduction to PostgreSQL TIME data type. PostgreSQL provides the TIME data type that allows you to store the time of day values. The following statement illustrates how to declare a column with the TIME

Understanding PostgreSQL Timestamp Data Types, This tutorial introduces you to the PostgreSQL TIME data type and some useful time related functions to handle time values effectively. 8.5.2. Date/Time Output. The output format of the date/time types can be set to one of the four styles ISO 8601, SQL (Ingres), traditional POSTGRES, and German, using the command SET datestyle. The default is the ISO format. (The SQL standard requires the use of the ISO 8601 format.

A Quick Guide To The PostgreSQL TIME Data Type, PostgreSQL has six different data types to represent date and times from which only two store a date and a time in a single data type: “timestamp without time zone  PostgreSQL provides two JSON data types: JSON and JSONB for storing JSON data. The JSON data type stores plain JSON data that requires reparsing for each processing, while JSONB data type stores JSON data in a binary format which is faster to process but slower to insert. In addition, JSONB supports indexing, which can be an advantage.

How to store dates and times in PostgreSQL, Datetime data types include DATE, TIMESTAMP, and TIMESTAMPTZ. Storage and ranges. Name, Storage, Range, Resolution. DATE, 4 bytes, 4713 BC to 294276  PostgreSQL provides two temporal data types for handling timestamp, one without timezone ( timestamp) and one with timezone ( timestamptz). The timestamp data type allows you to store both date and time. However, it does not have any time zone data.

Comments
  • Tell us a bit more about what you want to do with this action. Are you managing database from SQL plain or using an ORM from Python?
  • So are you asking for the best way to store the Postgres date type in a python variable is?
  • @JuanCastaño now im creating database structure, so just typing SQL, without python. im not sure, should i use database time, or python system time.
  • @K-Log im not sure, should i use database time, or python system time.
  • Well it depends on what you want to do with it. For example, if you use python’s datetime object it allows for easy manipulation and accessing of specific components of the object such as just the weekday or the year.