How to convert date format in PostgreSQL

[Chittaranjan]

Hi All,

I am not sure how to change the date format in PostgreSQL. I have the dates stored in the database as

yyyy-mm-dd HH:MM:SS+TimeZone to get the GMT time (Ex: 2008-02-28 14:03:23+05:30)

. But I want these dates to be shown in the following format:

dd/mm/yyyy HH:MM:SS TimeZone (Ex: 28/02/2008 14:03:23 IST)

So now while I am trying to fetch the dates from the database I am using to_char() as below:

to_char(field_name, 'DD/MM/YYYY HH24:MI:SS TZ') AS field_name

In this way I am able to get the required format but the problem I am facing is when I am trying to use this information then I think it is treating the dates as characters so I am not able to get required result.
For Ex: When I am trying to sort the output records with the dates then it is not sorting properly.

So can anybody give some idea or example how to do this in other way and get rid of my issue.

Thanks and Regards,
Chittaranjan :)

[rski]

Hi All,

I am not sure how to change the date format in PostgreSQL. I have the dates stored in the database as yyyy-mm-dd HH:MM:SS+TimeZo ne to get the GMT time (Ex: 2008-02-28 14:03:23+05:30). But I want these dates to be shown in the following format:
dd/mm/yyyy HH:MM:SS TimeZone (Ex: 28/02/2008 14:03:23 IST). So now while I am trying to fetch the dates from the database I am using to_char() as below:
to_char(field_n ame, 'DD/MM/YYYY HH24:MI:SS TZ') AS field_name

In this way I am able to get the required format but the problem I am facing is when I am trying to use this information then I think it is treating the dates as characters so I am not able to get required result.
For Ex: When I am trying to sort the output records with the dates then it is not sorting properly.

So can anybody give some idea or example how to do this in other way and get rid of my issue.

Thanks and Regards,
Chittaranjan :)

Is this

set datestyle to 'sql,european';
 

what you are looking for?
Or you can set

alter user username set datestyle to 'sql,european';
 

to save the setting for the next time you connect to postgres

[Chittaranjan]

Is this

set datestyle to 'sql,european';
 

what you are looking for?
Or you can set

alter user username set datestyle to 'sql,european';
 

to save the setting for the next time you connect to postgres

Hi rski,

Thanks a lot for your quick response. The examples you provided are very valuable but this is for changing the entire date style of the database tables. But I want to change the date format to European style while fetching the records from the database to show on the front end.

Please let me know if you or any one had this this kind of problem ever or the solution for this.

Your valuable responses are highly appreciated.

Thanks and Regards,
Chittaranjan :)

[rski]

Hi rski,

Thanks a lot for your quick response. The examples you provided are very valuable but this is for changing the entire date style of the database tables. But I want to change the date format to European style while fetching the records from the database to show on the front end.

Please let me know if you or any one had this this kind of problem ever or the solution for this.

Your valuable responses are highly appreciated.

Thanks and Regards,
Chittaranjan :)

Maybe write a plpgsql function like that

create or replace function date_convert(varchar) returns date as
$$
declare
begin
execute 'set datestyle to '||'' || $1||'';
return now()::date;
end;
$$ language 'plpgsql'
 

and call it in php script for example

$query="select date_convert('german')";
$result=pg_query($connect,$query);
 

is it helpful?