CODEWITHSUNDEEP
postgresqldatetimezonetimestamp-with-timezone
snoob dogg
snoob dogg

Reputation: 2865

PostGreSQL : date_trunc() returns timestamp with timezone when used on date

I'm doing some tests, I've created the following table :

enter image description here

When I execute the following request :

SELECT date_trunc('month', my_date) FROM  my_table;

it returns that :

enter image description here

I do understand it is a timestamp as stated by the PostGreSQL reference but I don't understand why it add these timezone and why there is that small variation between +01 or +02 ? How are they choosed ? is it a bug ?

Upvotes: 1

Views: 3510

Answers (1)

Jasen
Jasen

Reputation: 12402

you're falling victim to date beind implicitly cast to timestamp with thome zone to enable the function call.

date_trunc is only defined for timestamp with time zone and timestamp inputs.

+01 +02 etc depends on your time locale's daylight saving rules.

if you want timestamp instead of timestamptz cast the date to timestamp first.

 SELECT date_trunc('month', cast(my_date as timestamp)) FROM my_table

or you can create your own function which takes a date and returns a date.

 create or replace function date_trunc(text,date) returns date language sql as
  'select date_trunc($1, $2::timestamp)::date';

Upvotes: 2

Related Questions