#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2000
    Location
    Los Angeles, California
    Posts
    102
    Rep Power
    15

    Question 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!
    Edgar Castanedo
  2. #2
  3. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    62
    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.
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2000
    Location
    Los Angeles, California
    Posts
    102
    Rep Power
    15
    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:

    to_char(time-string, 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:

    http://www.commandprompt.com/ppbook/...ap=x8973%2ehtm

    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.
    Edgar Castanedo
  6. #4
  7. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    62

    Talking


    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).
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2002
    Posts
    409
    Rep Power
    13

    Thumbs up


    The Postgres date functions totally kick ***. Plus they are more compatible with other databases such as Oracle, Informix, etc.

    rycamor,

    I think it's time you wrote a book. Seriously!
  10. #6
  11. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    62
    Originally posted by Ted Striker
    rycamor,

    I think it's time you wrote a book. Seriously!
    Find me a sponsor, and I will .
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2002
    Posts
    409
    Rep Power
    13

    Cool


    rycamor!

    Be careful what you ask for!!!

    Check your private messagesl.

    If you don't have private messages turned on, then
    let me know.
  14. #8
  15. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    62

    Talking


    I had to open my big mouth...
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com

IMN logo majestic logo threadwatch logo seochat tools logo