Why TimeStamp was truncated?
This is a discussion on Why TimeStamp was truncated? within the MySQL forums, part of the Database Server Software category; --> CREATE TABLE `table1` ( `ts` TIMESTAMP NOT NULL ) ENGINE = innodb; INSERT INTO `table1` (`ts`) VALUES ('1202832067'); INSERT ...
| |||||||
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
02-28-2008, 01:59 PM
| ||||
| ||||
 Why TimeStamp was truncated? 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?      |
|
02-28-2008, 01:59 PM
| |||
| |||
| Re: Why TimeStamp was truncated? On Thu, 28 Feb 2008 11:43:18 +0100, howa <howachen@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 |
|
03-01-2008, 02:49 PM
| |||
| |||
| Re: Why TimeStamp was truncated? 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 |
|
03-01-2008, 02:49 PM
| |||
| |||
| Re: Why TimeStamp was truncated? On Thu, 28 Feb 2008 16:58:50 +0100, howa <howachen@gmail.com> wrote: > On 2月28日, 下午6時48分, "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') ); > > 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? Add an int column ('timestampcolumn') to the table, load it in there, and then run: UPDATE table SET datetimecolumn = FROM_UNIXTIME(timestampcolumn); -- Rik Wasmus |
|
03-01-2008, 02:49 PM
| |||
| |||
| Re: Why TimeStamp was truncated? On 28 Feb, 16:05, "Rik Wasmus" <luiheidsgoe...@hotmail.com> wrote: > On Thu, 28 Feb 2008 16:58:50 +0100, howa <howac...@gmail.com> 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') ); > > > 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? > > Add an int column ('timestampcolumn') to the table, load it in there, and > then run: > UPDATE table SET datetimecolumn = FROM_UNIXTIME(timestampcolumn); > -- > Rik Wasmus But this still invokes the method once per row. |
|
03-01-2008, 02:49 PM
| |||
| |||
| Re: Why TimeStamp was truncated? On Thu, 28 Feb 2008 17:12:11 +0100, Captain Paralytic <paul_lautman@yahoo.com> wrote: > On 28 Feb, 16:05, "Rik Wasmus" <luiheidsgoe...@hotmail.com> wrote: >> On Thu, 28 Feb 2008 16:58:50 +0100, howa <howac...@gmail.com> wrote: >> > On 2月28日, 下午6時48分, "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') ); >> >> > 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? >> >> Add an int column ('timestampcolumn') to the table, load it in there, >> and >> then run: >> UPDATE table SET datetimecolumn = FROM_UNIXTIME(timestampcolumn); > > But this still invokes the method once per row. It would, I'm just assuming (perhaps in error) that the OP has some import to do, from something like a dump or csv file, where it's pretty hard to get it to run for every row. If not, this method indeed surely only adds problems rather then solving them. -- Rik Wasmus |
|
03-01-2008, 02:49 PM
| |||
| |||
| Re: Why TimeStamp was truncated? 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. jstucklex@attglobal.net ================== |
|
03-01-2008, 02:49 PM
| |||
| |||
| Re: Why TimeStamp was truncated? 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. |
|
03-01-2008, 02:49 PM
| |||
| |||
| Re: Why TimeStamp was truncated? 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. jstucklex@attglobal.net ================== |
|
03-01-2008, 02:49 PM
| ||||
| ||||
| Re: Why TimeStamp was truncated? 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 |
Linear Mode
