SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Enthusiast
    Join Date
    Dec 2006
    Location
    Dubrovnik, Croatia
    Posts
    82
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL date storing

    Hi,

    Question : I need to store date only in 'dd.mm.' format, in my case i don't need year. For example i want to have table seasons defined :

    Code:
    season_code  from       to
    firstq             02.04.     01.06.
    secq              02.06.     28.08.
    thirq              29.08.     01.10.
    So i can handle this from and to fields as date.

  2. #2
    SitePoint Enthusiast
    Join Date
    Dec 2006
    Location
    Dubrovnik, Croatia
    Posts
    82
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I can do something like
    DATE_FORMAT(str_to_date(rsp.date_from , '%d.%m.'), '%d.%m.') , is this ok?

  3. #3
    SitePoint Member temmokan's Avatar
    Join Date
    Dec 2009
    Location
    Novosibirsk, Russia
    Posts
    23
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Do you ask what field type to use?

    To me, this is a question of functionality. If you, say, perform a lot of dates comparison (which is earlier/later), I could suggest using, say, an integer value (and store day/month as, say, 100*month +day).

    If, however, more complex date manipulation/comparison is used, perhaps the built-in date type will do, where 'year' part is constant across all the values.

    So the question is what operations with date values are expected ?
    Konstantin Boyandin
    Network and Server Monitoring Tool: uptime/performance monitoring

  4. #4
    SitePoint Enthusiast
    Join Date
    Dec 2006
    Location
    Dubrovnik, Croatia
    Posts
    82
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I need to find out what season is defined based on incoming date. That date must be between from and to field so i can extract season_code.

  5. #5
    SitePoint Member temmokan's Avatar
    Join Date
    Dec 2009
    Location
    Novosibirsk, Russia
    Posts
    23
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by djomla View Post
    I need to find out what season is defined based on incoming date. That date must be between from and to field so i can extract season_code.
    The proposed integer month/day storage would make the lookup quite efficient (integer comparison only, the BETWEEN operator would be very quick).
    Konstantin Boyandin
    Network and Server Monitoring Tool: uptime/performance monitoring

  6. #6
    SitePoint Enthusiast
    Join Date
    Dec 2006
    Location
    Dubrovnik, Croatia
    Posts
    82
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes i think so ... and quite easy

    Thank you

  7. #7
    SitePoint Member temmokan's Avatar
    Join Date
    Dec 2009
    Location
    Novosibirsk, Russia
    Posts
    23
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by djomla View Post
    Yes i think so ... and quite easy

    Thank you
    You are welcome. There were cases when I needed efficient date records storage AND comparison; if no convoluted date manipulation is assumed, I often use the YYYYMMDD-packed format.

    Good luck.
    Last edited by temmokan; Dec 16, 2009 at 04:13. Reason: typo
    Konstantin Boyandin
    Network and Server Monitoring Tool: uptime/performance monitoring


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •