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

Expand|Select|Wrap|Line Numbers

 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:

Expand|Select|Wrap|Line Numbers

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:

Expand|Select|Wrap|Line Numbers

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

Quote:

Originally Posted by 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 :)

Is this

Expand|Select|Wrap|Line Numbers

set datestyle to 'sql,european';

what you are looking for?
Or you can set

Expand|Select|Wrap|Line Numbers

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

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

Chittaranjan

Quote:

Originally Posted by rski

Is this

Expand|Select|Wrap|Line Numbers

set datestyle to 'sql,european';

what you are looking for?
Or you can set

Expand|Select|Wrap|Line Numbers

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

Quote:

Originally Posted by Chittaranjan

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

Expand|Select|Wrap|Line Numbers

 
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

Expand|Select|Wrap|Line Numbers

 
$query="select date_convert('german')";

$result=pg_query($connect,$query);

is it helpful?

How to convert date format in PostgreSQL

re: How to convert date format in PostgreSQL

re: How to convert date format in PostgreSQL

re: How to convert date format in PostgreSQL

Similar PostgreSQL Database bytes

/bytes/development

/bytes/it

/bytes/about