[support] How to store/retreive date fields into/from MySql

[Carl Wiedemann]

If you are storing birthdates, you probably want to use DATETIME in the
schema instead of INT. INT would require the date be stored as a unix
timestamp, which is only valid after the year 1970, and birthdays are likely
to exist before this date.

DATETIME accepts strings formatted like

YYYY-MM-DD HH:MM:SS

and generally you store the UTC date and convert the timezone later.

See some of the functions in the Date API module for help in conversion
(comes with Date module). Some of these utilities have been moved into core
in Drupal 7.


On Mon, Mar 28, 2011 at 9:27 AM, Brett Evanson <brettev at gmail.com> wrote:

> You might want to look at the strtotime function in php. I believe it
> would take that date and turn it into a Unix epoch number (i think
> that's what you're looking for)
>
> Brett Evanson
>
>
>
>
>
> On Mon, Mar 28, 2011 at 9:15 AM, Austin Einter <austin.einter at gmail.com>
> wrote:
> > Hi All
> > I have date field, I am storing it as "int" in table.
> > When I checked the table in database, the value shows as 1.
> >
> > My code is as below.
> >
> > 'INSERT INTO {rs_table} ( uid, name, qualification, email, altemail,
> phone,
> > altphone, dob, yrsofexp, prefworkloc) '
> >       ."VALUES (%d, '%s', '%s', '%s', '%s', '%s', '%s', '%d', '%d',
> '%s')",
> >      $user->uid,
> >      $form_state['values']['persname'],
> >      $form_state['values']['hqualif'],
> >      $form_state['values']['email'],
> >      $form_state['values']['altemail'],
> >      $form_state['values']['phone'],
> >      $form_state['values']['altphone'],
> >     $form_state['values']['birthdate'],
> >     $form_state['values']['yearsexp'],
> >     $form_state['values']['prefloc']
> >   );
> >
> > Whats the wrong here, is there any API I have to make use of to convert
> from
> > date to int value??
> >
> > Regards
> > Austin
> >
> >
> >
> > On Mon, Mar 28, 2011 at 8:36 AM, Austin Einter <austin.einter at gmail.com>
> > wrote:
> >>
> >> Hi All
> >> I have a date field in my form.
> >> How do I store/retreive the value into/from MySQL.
> >>
> >> On google search, I saw people are suggesting to use either "int" or
> >> "datetime" type.
> >> Please suggest which one needs to be used.
> >>
> >> If I use int (I hope core uses int), while storing can I just
> >> store "form['values']['date1']" as %d and while retreivin, what are the
> >> related apis to convert back to date.
> >>
> >> Regards
> >> Austin.
> >>
> >>
> >
> > --
> > [ Drupal support list | http://lists.drupal.org/ ]
> >
> --
> [ Drupal support list | http://lists.drupal.org/ ]
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.drupal.org/pipermail/support/attachments/20110328/c9760e13/attachment.html