By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,530 Members | 880 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,530 IT Pros & Developers. It's quick & easy.

How to convert date format in PostgreSQL

Chittaranjan
P: 51
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
  1. 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
  1. 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
  1. 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 :)
Feb 28 '08 #1
Share this Question
Share on Google+
3 Replies


Expert 100+
P: 700
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
  1. set datestyle to 'sql,european';
  2.  
what you are looking for?
Or you can set
Expand|Select|Wrap|Line Numbers
  1. alter user username set datestyle to 'sql,european';
  2.  
to save the setting for the next time you connect to postgres
Feb 28 '08 #2

Chittaranjan
P: 51
Is this
Expand|Select|Wrap|Line Numbers
  1. set datestyle to 'sql,european';
  2.  
what you are looking for?
Or you can set
Expand|Select|Wrap|Line Numbers
  1. alter user username set datestyle to 'sql,european';
  2.  
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 :)
Feb 29 '08 #3

Expert 100+
P: 700
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
  1. create or replace function date_convert(varchar) returns date as
  2. $$
  3. declare
  4. begin
  5. execute 'set datestyle to '||'' || $1||'';
  6. return now()::date;
  7. end;
  8. $$ language 'plpgsql'
  9.  
and call it in php script for example
Expand|Select|Wrap|Line Numbers
  1. $query="select date_convert('german')";
  2. $result=pg_query($connect,$query);
  3.  
is it helpful?
Mar 1 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.