September 29th, 2002, 10:19 PM
working with dates
I just recently started with PostgreSQL. I come from a MySQL background. I am wondering if there is a way to format dates in PostgreSQL like the DATE_FORMAT() function in MySQL.
Thanks in Advance!
September 29th, 2002, 10:56 PM
Prepare to be amazed: this is one area where Postgres really shines. The syntax for date manipulation is different, but in the end you will find out it is much more powerful and flexible. (it even has a human-readable datatype called 'interval', which looks like '1 year 2 months 3 days 5 hours', etc...)
Unfortunately, the documentation is not completely intuitive on all the possibilities with date formatting and calculations, but here are a few places to start, and you can just post back here when you run into trouble: (read the user notes at the bottom also)
1. Basic Info
2. Read the Date/Time datatype section
3. Read "Date/Time Functions and Operators", "Data Type Formatting Functions", and "String Functions and Operators"
With PostgreSQL, it is very easy to use string functions on a date, because any column can be cast as another datatype, using the :: operator, such as NOW()::text, which casts the current date as a text string, so any string function can now enclose it, such as length(NOW()::text).
Also, you will find that unlike MySQL, PostgreSQL has full constraints for Date column types -- I had a very difficult time with the last MySQL database I worked on, because the whole database was about time differences in events, and I found that MySQL has several bad habits with time data:
a. Wrong dates can be entered. For example, one can enter a date of 0000-00-00. This is wrong because no year has a 0 month, and no month has a 0 day. Also, it allows up to 31 days for ANY month, even February, which should only have 28 days. (when you think about it, this means every month can actually have 32 days in MySQL, because it starts at 0).
b. No truly good methods for measuring date intervals (of course, MySQL intervals are ambiguous anyway, given the above problems). PostgreSQL was a lifesaver here, because it actually has an "interval" datatype, that shows time intervals in human-readable format, such as "2 Years 5 Months 23 Days 18:03:23". This, combined with PostgreSQL's date calculation and comparison features, make it a great database for handling statistical, event-based data.
October 7th, 2002, 06:30 PM
Thanks for your reply. Apparently, there are many differences between MySQL and PostgreSQL. At first I was disappointed when I didn't find a function like DATE_FORMAT() in MySQL, and almost gave up.
Finally, after insisting that there had to be something like this in PostgreSQL, I found the to_char function. In this format:
it performs the same type of date formatting operation as DATE_FORMAT. I believe that this was the last thing that I was holding onto as a reason to not switch from one database to another.
If anyone would like to see the syntax to using this function, go here:
As a matter of fact, this is a great online book which also happens to be available in hard-copy.
Thanks again for the help.
October 7th, 2002, 10:26 PM
Ooops, yes... I now see that I forgot to include that one.
With this, plus the other formatting methods available, you can really do anything with your date formats. The great thing is: define one of those formats in a view, and then you don't need to think about it again for the rest of your application. (benefit of a real relational DBMS).
October 7th, 2002, 11:22 PM
The Postgres date functions totally kick ***. Plus they are more compatible with other databases such as Oracle, Informix, etc.
I think it's time you wrote a book. Seriously!
October 8th, 2002, 03:08 AM
Find me a sponsor, and I will .
October 8th, 2002, 01:49 PM
Be careful what you ask for!!!
Check your private messagesl.
If you don't have private messages turned on, then
let me know.
October 8th, 2002, 02:27 PM
I had to open my big mouth...