[Solved] Inserting timestamp values

Discuss the database features

[Solved] Inserting timestamp values

Postby caravas » Sun Sep 08, 2013 3:51 am

Hi folks,

Long time no see...
I' m tring to insert some timestamp values iinto my database using this SQL command:

INSET INTO MYTABLE (ID, DATE) VALUES ('4', CURRENT_TIMESTAMP)

The following values are inserted into mytable:
4, 07/09/2013 00:00

It doesn't matter what values are placed, only time 00:00 is stored into mytable.

Any suggestions?
Thanks!

LibreOffice 4.1.0.4
Windows 8
HSQLDB 2.3.0 (external datasource)
Last edited by caravas on Sun Sep 08, 2013 10:41 pm, edited 1 time in total.
LO 4.3.4 on Windows 8.1
User avatar
caravas
 
Posts: 36
Joined: Sun Jul 15, 2012 3:53 am

Re: Inserting timestamp values

Postby Villeroy » Sun Sep 08, 2013 1:53 pm

I think it is a display problem with LibreOffice 4.1. The time is stored properly but the GUI elements do not show the stored value.
No, I confused this with another LO problem.
Last edited by Villeroy on Sun Sep 08, 2013 5:24 pm, edited 1 time in total.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27718
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Inserting timestamp values

Postby F3K Total » Sun Sep 08, 2013 5:03 pm

Hi,
i tested Windows 7, HSQLDB 2.3.0 external, LO 4.1.1.2
The result looks as it should:
TS.png
TS.png (4.11 KiB) Viewed 5176 times

You're sure, that column DATE is a TIMESTAMP column?
Your result let's me guess, that it's a DATE column, which has no time.
R
    MMove 1.0.6
  • Extension for easy, exact positioning of shapes, pictures, controls, frames ...
    my current system
  • Windows 10 AOO, LOLinux Mint AOO, LO
F3K Total
Volunteer
 
Posts: 950
Joined: Fri Dec 16, 2011 8:20 pm

Re: Inserting timestamp values

Postby caravas » Sun Sep 08, 2013 7:30 pm

It is a Timestamp column: "12/12/13 00:00".
I guess Villeroy got the point, because of calculations using the values of the column DATE are rigth (DATEDIFF('mm', DATE_A, DATE_B).
I'll try to update LO to the last version available..
I'll let you know the results.
Thanks!
LO 4.3.4 on Windows 8.1
User avatar
caravas
 
Posts: 36
Joined: Sun Jul 15, 2012 3:53 am

Re: Inserting timestamp values

Postby F3K Total » Sun Sep 08, 2013 7:37 pm

Hi,
if you would load up a short version of your database Folder (.script, .properties, .data) i could test it with LO 4.1.1/HSQLDB2.3.0
R
    MMove 1.0.6
  • Extension for easy, exact positioning of shapes, pictures, controls, frames ...
    my current system
  • Windows 10 AOO, LOLinux Mint AOO, LO
F3K Total
Volunteer
 
Posts: 950
Joined: Fri Dec 16, 2011 8:20 pm

Re: Inserting timestamp values

Postby Sliderule » Sun Sep 08, 2013 7:58 pm

Forgive me for mentioning this, but, in your original post here, you said you used the SQL INSERT statement:

Code: Select all   Expand viewCollapse view
NSERT INTO MYTABLE (ID, DATE) VALUES ('4', CURRENT_TIMESTAMP)


Just as an FYI ( For Your Information ) . . . using DATE as a Column ( field ) name is permitted, BUT it is a a KEYWORD . . . see the following link:

http://www.hsqldb.org/doc/2.0/guide/lists-app.html#lta_standard_keywords

HSQL Documentation: http://www.hsqldb.org/doc/2.0/guide/lists-app.html#lta_standard_keywords wrote:
All keywords can be used with double quotes as identifiers. For example

Code: Select all   Expand viewCollapse view
CREATE TABLE "ALL" ("AND" INT, "WHEN" INT)
SELECT "AND" FROM "ALL" WHERE "WHEN" = 2020


Therefore, the requirement is . . . you MUST surround it ( your defined keyword . . . DATE ) with double quotes ( "DATE" ) . . . so . . . the database back-end engine ( HSQL, 2.3.0 ) knows you are referring to a column name. :)

What I am suggesting you try is:

Code: Select all   Expand viewCollapse view
INSERT INTO MYTABLE (ID, "DATE") VALUES ('4', CURRENT_TIMESTAMP)


Just a thought, I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this isue has been resolved.
User avatar
Sliderule
Volunteer
 
Posts: 1205
Joined: Thu Nov 29, 2007 9:46 am

Re: Inserting timestamp values

Postby caravas » Sun Sep 08, 2013 10:22 pm

Hi Sliderule!

I've just translated the name of my table columns and did not realized the conflict of names. The real names are: ID, ENTRADA, SAIDA (it means ENTER AND EXIT in Portuguese). Sorry I miss pointed you :?

ID column is integer auto increased.
ENTRADA and SAIDA are Timestamp (well. at least I do believe in that..)
I tried to insert the values by SQL command and typing straight on Base's Data Grid. The behavior is exactly the same in both ways: the Time part is missed, and turn to "00:00".
Using the following SQL code, gives me the right value, so, the information is stored in the database but, somehow, Base do not show the information concerning the Time piece:

Code: Select all   Expand viewCollapse view
DATEDIFF('mm', ENTRADA, SAIDA) AS TOTAL_MINUTES


I uploaded the database file. I woul be glad if you don't mind to take a look into.

Thanks to all!
Attachments
horas12srprf-20130908T171951.tar
BACKUP OF THE DATABASE DRAFT
(6 KiB) Downloaded 64 times
LO 4.3.4 on Windows 8.1
User avatar
caravas
 
Posts: 36
Joined: Sun Jul 15, 2012 3:53 am

Re: Inserting timestamp values

Postby caravas » Sun Sep 08, 2013 10:40 pm

Well,

LibreOffice 4.1.1.2 worked just fine. The question was about the old version I had: LibreOffice 4.1.0.4.

Thanks Villeroy, Sliderule and F3K Total!
LO 4.3.4 on Windows 8.1
User avatar
caravas
 
Posts: 36
Joined: Sun Jul 15, 2012 3:53 am

Re: Inserting timestamp values

Postby F3K Total » Sun Sep 08, 2013 10:43 pm

Hi,
i got the same result with your DB as mentioned above:
1.png

Windows 7, HSQLDB 2.3.0 external, LO 4.1.1, Java 7.25
EDIT: OK, just saw your success-post ...
R
    MMove 1.0.6
  • Extension for easy, exact positioning of shapes, pictures, controls, frames ...
    my current system
  • Windows 10 AOO, LOLinux Mint AOO, LO
F3K Total
Volunteer
 
Posts: 950
Joined: Fri Dec 16, 2011 8:20 pm

Re: [Solved] Inserting timestamp values

Postby Sliderule » Sun Sep 08, 2013 10:57 pm

Just as an FYI ( For Your Information ) . . . for others reading this, the code below, as written:

Code: Select all   Expand viewCollapse view
DATEDIFF('mm', ENTRADA, SAIDA) AS TOTAL_MINUTES


since the first parameter of the function DATEDIFF was coded as 'mm' it will return the number of months between the two timestamp values. If, on the other hand, you wanted to return the number of minutes between the two timestamp values, it could be entered as EITHER:

Code: Select all   Expand viewCollapse view
DATEDIFF('mi', ENTRADA, SAIDA) AS TOTAL_MINUTES

DATEDIFF('minute', ENTRADA, SAIDA) AS TOTAL_MINUTES


You can read about this at the link below:

http://www.hsqldb.org/doc/2.0/guide/builtinfunctions-chapt.html wrote:
DATEDIFF

DATEDIFF ( <field>, <datetime value expr 1>, <datetime value expr 2> )

<field> ::= 'yy' | 'year' | 'mm' | 'month' | 'dd' | 'day' | 'hh' | 'hour' | 'mi' | 'minute' | 'ss' | 'second' | 'ms' | 'millisecond'

The DATEADD and DATEDIFF functions are alternatives to TIMESTAMPADD and TIMESTAMPDIFF, with fewer available field options. The field names are specified as strings, rather than keywords. The fields translate to YEAR, MONTH, DAY, HOUR, MINUTE, SECOND and MILLISECOND. DATEDIFF is evaluated as <datetime value expr 2> - <datetime value expr 1>. (HyperSQL}


Sliderule
User avatar
Sliderule
Volunteer
 
Posts: 1205
Joined: Thu Nov 29, 2007 9:46 am

Re: [Solved] Inserting timestamp values

Postby caravas » Sun Sep 08, 2013 11:27 pm

Sliderule wrote:Just as an FYI ( For Your Information ) . . . for others reading this, the code below, as written:

Code: Select all   Expand viewCollapse view
DATEDIFF('mm', ENTRADA, SAIDA) AS TOTAL_MINUTES


since the first parameter of the function DATEDIFF was coded as 'mm' it will return the number of months between the two timestamp values. If, on the other hand, you wanted to return the number of minutes between the two timestamp values, it could be entered as EITHER:

Code: Select all   Expand viewCollapse view
DATEDIFF('mi', ENTRADA, SAIDA) AS TOTAL_MINUTES

DATEDIFF('minute', ENTRADA, SAIDA) AS TOTAL_MINUTES


You can read about this at the link below:

http://www.hsqldb.org/doc/2.0/guide/builtinfunctions-chapt.html wrote:
DATEDIFF

DATEDIFF ( <field>, <datetime value expr 1>, <datetime value expr 2> )

<field> ::= 'yy' | 'year' | 'mm' | 'month' | 'dd' | 'day' | 'hh' | 'hour' | 'mi' | 'minute' | 'ss' | 'second' | 'ms' | 'millisecond'

The DATEADD and DATEDIFF functions are alternatives to TIMESTAMPADD and TIMESTAMPDIFF, with fewer available field options. The field names are specified as strings, rather than keywords. The fields translate to YEAR, MONTH, DAY, HOUR, MINUTE, SECOND and MILLISECOND. DATEDIFF is evaluated as <datetime value expr 2> - <datetime value expr 1>. (HyperSQL}


Sliderule


Thank you again my friend!
You are right, as usually...
Thank you again my friend!
LO 4.3.4 on Windows 8.1
User avatar
caravas
 
Posts: 36
Joined: Sun Jul 15, 2012 3:53 am


Return to Base

Who is online

Users browsing this forum: No registered users and 4 guests