On 2$B7n(B29$BF|(B, $B>e8a(B10$B;~(B19$BJ,(B, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> howa wrote:
> > On 2$B7n(B29$BF|(B, $B>e8a(B12$B;~(B30$BJ,(B, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> >> howa wrote:
> >>> On 2$B7n(B28$BF|(B, $B2<8a(B6$B;~(B48$BJ,(B, "Rik Wasmus" <luiheidsgoe...@hotmail.com> wrote:
> >>>> On Thu, 28 Feb 2008 11:43:18 +0100, howa <howac...@gmail.com> wrote:
> >>>>> CREATE TABLE `table1` (
> >>>>> `ts` TIMESTAMP NOT NULL
> >>>>> ) ENGINE = innodb;
> >>>>> INSERT INTO `table1` (`ts`) VALUES ('1202832067');
> >>>>> INSERT INTO `table1` (`ts`) VALUES ( UNIX_TIMESTAMP('1202832067') );
> >>>>> The table result is storing "0000-00-00 00:00:00" for both queries,
> >>>>> any idea?
> >>>> Because MySQL doesn't automatically convert an integer to a datetime
> >>>> string.
> >>>> INSERT INTO `table1` (`ts`) VALUES ( FROM_UNIXTIME('1202832067') );
> >>>> --
> >>>> Rik Wasmus
> >>> Hello, If I have millions of row of UNIX timestamps to insert, I don't
> >>> want to invoke the method everytime, are there any faster method?
> >>> Thx
> >> If the timestamps are different, you need to call FROM_UNIXTIME for each
> >> one. It's the only way you're going to get the correct timestamp for
> >> each row.
>
> >> However, if they are all identical, you could do something like:
>
> >> SET @mytimestamp=FROM_UNIXTIME('1202832067');
> >> INSERT INTO table1 (ts) VALUES (@mytimestamp));
>
> >> --
> >> ==================
> >> Remove the "x" from my email address
> >> Jerry Stuckle
> >> JDS Computer Training Corp.
> >> jstuck...@attglobal.net
> >> ==================
>
> > Hi all,
>
> > Given that the limitations, I would rather store the timestamp as
> > INT(10) unsigned.
>
> > Thanks.
>
> Which is worse - converting to a timestamp once when you put it in, or
> every time you pull it out?
>
> --
> ==================
> Remove the "x" from my email address
> Jerry Stuckle
> JDS Computer Training Corp.
> jstuck...@attglobal.net
> ==================

Yes, I just need to import those timestamp everyday but query quite
rarely.

Besides, I just found out some interesting comments from wikipedia's
guy...

-- The MySQL table backend for MediaWiki currently uses
-- 14-character BINARY or VARBINARY fields to store timestamps.
-- The format is YYYYMMDDHHMMSS, which is derived from the
-- text format of MySQL's TIMESTAMP fields.
--
-- Historically TIMESTAMP fields were used, but abandoned
-- in early 2002 after a lot of trouble with the fields
-- auto-updating.


Howard