Hi, Which timezone are you in (I guess somewhere in South America)? Probably the given time (midnight that day) doesn't exist there, because of a daylight saving time change at midnight.
Which version of H2 do you use? Recent versions of H2 should have that fixed, at least if you don't use any Java functions to format the time (so at least the first column should be correct). For the output you need, you don't actually need formatdatetime - you could use cast(x as date). Regards, Thomas On Wed, Oct 23, 2013 at 2:58 PM, Marcelo Goncalves <[email protected]>wrote: > Hi there, > > After this sequence of commands: > ------------------------------------------------------- > DROP TABLE datahoratest CASCADE ; > > CREATE TABLE datahoratest > ( > id INTEGER AUTO_INCREMENT, > saledatetime TIMESTAMP NOT NULL > ); > > ALTER TABLE datahoratest > ADD CONSTRAINT PRIMARY_KEY_5 > PRIMARY KEY (id); > > CREATE INDEX xak1datahoratest > ON datahoratest (saledatetime ASC); > > insert into datahoratest(saledatetime) values ('2013-10-20 00:00:00'); > insert into datahoratest(saledatetime) values ('2013-10-20'); > insert into datahoratest(saledatetime) values ('2013-10-19 00:00:00'); > insert into datahoratest(saledatetime) values ('2013-10-19'); > insert into datahoratest(saledatetime) values ('2013-10-17 00:00:00'); > insert into datahoratest(saledatetime) values ('2013-10-17'); > > select saledatetime, > FORMATDATETIME(saledatetime,'yyyy-MM-dd') as dateonly, > CONCAT(FORMATDATETIME(saledatetime,'yyyy-MM-dd'),' 00:00:00') as > dateonlyconcatzeros, > PARSEDATETIME(CONCAT(FORMATDATETIME(saledatetime,'yyyy-MM-dd'),' > 00:00:00'),'yyyy-MM-dd HH:mm:ss') AS newdatetime > from datahoratest; > ------------------------------------------------------- > > I receive this resultset: > > SALEDATETIME DATEONLY DATEONLYCONCATZEROS NEWDATETIME > 2013-10-20 01:00:00 2013-10-20 2013-10-20 00:00:00 2013-10-20 01:00:00 > 2013-10-20 01:00:00 2013-10-20 2013-10-20 00:00:00 2013-10-20 01:00:00 > 2013-10-19 00:00:00 2013-10-19 2013-10-19 00:00:00 2013-10-19 00:00:00 > 2013-10-19 00:00:00 2013-10-19 2013-10-19 00:00:00 2013-10-19 00:00:00 > 2013-10-17 00:00:00 2013-10-17 2013-10-17 00:00:00 2013-10-17 00:00:00 > 2013-10-17 00:00:00 2013-10-17 2013-10-17 00:00:00 2013-10-17 00:00:00 > > The problem occurs with 2013-10-20. In this day I dont have to save > timestamp with 0 hours 0 minutes 0 seconds ... everytime its save 01:00:00 > am !! ever !!! > I try in 3 diferentes machines, using 3 distributions of linux ( centos, > open-suse and ubuntu ) using Java 1.6.0_41 and H2-1.3.174.jar. > > Can i have any ideia to resolve this ? > > tks a lot > > -- > You received this message because you are subscribed to the Google Groups > "H2 Database" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > To post to this group, send email to [email protected]. > Visit this group at http://groups.google.com/group/h2-database. > For more options, visit https://groups.google.com/groups/opt_out. > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/groups/opt_out.
