Extract Date (Yyyy/Mm/Dd) from a Timestamp in Postgresql

Extract date (yyyy/mm/dd) from a timestamp in PostgreSQL

You can cast your timestamp to a date by suffixing it with ::date. Here, in psql, is a timestamp:

# select '2010-01-01 12:00:00'::timestamp;
      timestamp      
---------------------
 2010-01-01 12:00:00

Now we'll cast it to a date:

wconrad=# select '2010-01-01 12:00:00'::timestamp::date;
    date    
------------
 2010-01-01

On the other hand you can use date_trunc function. The difference between them is that the latter returns the same data type like timestamptz keeping your time zone intact (if you need it).

=> select date_trunc('day', now());
       date_trunc
------------------------
 2015-12-15 00:00:00+02
(1 row)

Converting timestamp to YYYY/MM/DD in POSTGRES

I think this is useful for you to understand Date Conversion.

SELECT Cast(Now() as Date) as YourDate;

SELECT Now()::date as YourDate;

Select To_Char(Now(),'yyyy/mm/dd') as YourDate;

Postgres Timestamp to DATE dd-mm-yyyy

If you need to convert formatted text to timestamp then use to_timestamp with explicit format specification. Try this:

select to_timestamp('02-09-2021 00:00:00', 'dd-mm-yyyy hh24:mi:ss');

to_timestamp
2021-02-09 00:00:00.000 +0200

Format date results of postgresql query to 'yyyymmdd'

Use to_char to format a timestamp. Pass it the timestamp to be formatted, and the format you want.

to_char(dim_study.created_datetime, 'YYYYMMDD') AS StudyDate

See Data Type Formatting Functions for details.

How to extract year and month from date in PostgreSQL without using to_char() function?

date_part(text, timestamp)

e.g.

date_part('month', timestamp '2001-02-16 20:38:40'),
date_part('year', timestamp '2001-02-16 20:38:40')

http://www.postgresql.org/docs/8.0/interactive/functions-datetime.html

Select date only without the hour on postgresql

Use date() function, it will extract date from datetime like:

select DATE(my_field) from my_table;

Easiest way to convert YYYYMMDD to the midnight with specific timezone on PostgreSQL?

Strictly speaking, your request is an oxymoron:

I want to get 2021-01-01 00:00:00.000000 in my TZ.

You show a timestamp (timestamp without time zone) literal, which is completely orthogonal to (and ignorant of) the concept of time zones.

But you want it "in my TZ", which would imply to a timestamptz (timestamp with time zone) value, where the corresponding literal includes a time offset like: 2021-01-01 00:00:00.000000+01.

Since the format YYYYMMDD is unambiguous ISO format, you can cast to date or timestamp directly, safely. A cast to timestamp assumes the time component 00:00 automatically. Produces your desired timestamp '2021-01-01 00:00'.

SELECT '20211203'::timestamp;

If you want the result type timestamp, we are done here.

If you want the result type timestamptz, there is a quick-and-dirty shortcut:

SELECT '20211203'::timestamptz;

The current time zone setting is assumed for the type cast. But this introduces a dependency on a runtime settings. Notoriously unreliable, only advisable for situations where you can be certain of the current setting ...

The sure and generally advisable way is to define the target time zone with the AT TIME ZONE construct explicitly. Say, your timezone is 'Europe/Vienna':

SELECT '20211203'::timestamp AT TIME ZONE 'Europe/Vienna';

Use a time zone name. Time zone abbreviations are treacherous for input conversion and may fail for daylight saving time (DST) or other bureaucratic nonsense. 'CET' (Central European Time) is appropriate for timestamps during "standard time". During DST periods, you'd have to use 'CEST' (Central European Summer Time).

_{DST is utter nonsense, but some countries, including the EU, still haven't managed to get rid of it.}

db<>fiddle here - note that dbfiddle runs with time zone GB by default.

Now you have the timestamptz value representing the start of the day (00:00) in your given time zone. Don't be fooled by the display of timestamptz values. That's always adjusted to the current time zone setting, but it always represents that unique point in time, just with different ways to display it.

You do understand that the time zone itself is never stored in a timestamptz value, right? Even though timestamp with time zone sounds like it might. See:

Time zone storage in data type "timestamp with time zone"

To force a certain display use to_char() or some other functions to generate the desired string. There is a dedicated page Data Type Formatting Functions in the manual.

https://www.postgresql.org/docs/current/view-pg-timezone-names.html
Ignoring time zones altogether in Rails and PostgreSQL

Extract Date (Yyyy/Mm/Dd) from a Timestamp in Postgresql