Get date from postgres as stated in database (without timezone offset)-CodePudding

I have a table in postgresql with a column named 'date_of_birth' as a date field.

In this, I have a value: '2000-01-01'.

The problem is, I don't get the correct value from it.

If I do this: SELECT date_of_birth FROM users, I get this result:

1999-12-31T23:00:00.000Z

I'm in timezone UTC 1. Timezone of the db is UTC (SHOW TIMEZONE).

I just need as a result: '2000-01-01'. NOT with the time zone.

Using SELECT date_of_birth::date FROM users gives me the same result.

CodePudding user response：

You may use the to_char() function of postgres

SELECT to_char(<column_name>, 'YYYY-MM-DD') as <column_name>
FROM <table_name>;

In your case it'll be:

SELECT to_char(date_of_birth, 'YYYY-MM-DD') as date_of_birth
FROM users;

CodePudding user response：

This is it: A quick fix:

SELECT TO_DATE(SUBSTRING(date_of_birth::varchar,1,10),'YYYY-MM-DD')::Date;