My TimeStamp field keeps populating though I don't write anything to it

classic Classic list List threaded Threaded
14 messages Options
Reply | Threaded
Open this post in threaded view
|

My TimeStamp field keeps populating though I don't write anything to it

Scott
I have a Comment table with the fields: CommentID, Summary, Comment, Pages, TimeStamp, Hyperlink. The TimeStamp field is setup as a Time(8), time with 8 characters only. Not every comment in this table requires a timestamp, so it should remain null. However, it has started adding "Thu Jan 01 00:00:00 EST 1970" and it will not let me clear it. This is being populated with the following Java code, in the past, it would just remain null. I included a Regular Expression to check the GUI field data when it is used. But, I can't even manually clear it now and I haven't changed the setup of the database, the table or TimeStamp field.
Any thoughts or suggestions?
String insertComment = "INSERT INTO Comment(Summary, Comment, Page, TimeStamp, Hyperlink) VALUES(?, ?, ?, ?, ?)";stmt = conn.prepareStatement(insertComment);stmt.setString(1,this.tbxSummary.getText());stmt.setString(2,this.tbxComment.getText());stmt.setString(3,this.tbxPages.getText());stmt.setString(4,this.tbxTimeStamp.getText());stmt.setString(5, this.tbxHyperlink.getText());stmt.execute()

Scott ValleryEcclesiastes 4:9-10
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: My TimeStamp field keeps populating though I don't write anything to it

Tim Streater-3
On 05 Feb 2019, at 13:08, Scott <[hidden email]> wrote:

> I have a Comment table with the fields: CommentID, Summary, Comment, Pages,
> TimeStamp, Hyperlink. The TimeStamp field is setup as a Time(8), time with 8
> characters only. Not every comment in this table requires a timestamp, so it
> should remain null. However, it has started adding "Thu Jan 01 00:00:00 EST
> 1970" and it will not let me clear it. This is being populated with the
> following Java code, in the past, it would just remain null. I included a
> Regular Expression to check the GUI field data when it is used. But, I can't
> even manually clear it now and I haven't changed the setup of the database,
> the table or TimeStamp field.
> Any thoughts or suggestions?

1) Please show your whole table definition for the comment table.

2) Time(8) has no meaning to SQLite, your timestamp field will probably default to TEXT.
   See: https://www.sqlite.org/datatype3.html



--
Cheers  --  Tim
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: My TimeStamp field keeps populating though I don't write anything to it

Scott
Hi Tim!
"Time(8) has no meaning to SQLite"

Thanks for the timely response. I provided below the details. "Time(8)" is the generated description of the structure defined by SQLite3 based upon Data Type options. So I apologize if I don't understand you comment.
Thanks!
Column Data Type Size----------------------------------------CommentID INTEGER PKSummary VARCHAR 120Comment BLOBPage VARCHAR 10TimeStamp TIME 8Hyperlink BLOB
Scott Vallery
Ecclesiastes 4:9-10

    On Tuesday, February 5, 2019, 8:23:53 AM EST, Tim Streater <[hidden email]> wrote:  
 
 On 05 Feb 2019, at 13:08, Scott <[hidden email]> wrote:

> I have a Comment table with the fields: CommentID, Summary, Comment, Pages,
> TimeStamp, Hyperlink. The TimeStamp field is setup as a Time(8), time with 8
> characters only. Not every comment in this table requires a timestamp, so it
> should remain null. However, it has started adding "Thu Jan 01 00:00:00 EST
> 1970" and it will not let me clear it. This is being populated with the
> following Java code, in the past, it would just remain null. I included a
> Regular Expression to check the GUI field data when it is used. But, I can't
> even manually clear it now and I haven't changed the setup of the database,
> the table or TimeStamp field.
> Any thoughts or suggestions?

1) Please show your whole table definition for the comment table.

2) Time(8) has no meaning to SQLite, your timestamp field will probably default to TEXT.
  See: https://www.sqlite.org/datatype3.html



--
Cheers  --  Tim
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
 
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: My TimeStamp field keeps populating though I don't write anything to it

Simon Slavin-3
On 5 Feb 2019, at 3:29pm, Scott <[hidden email]> wrote:

> Thanks for the timely response. I provided below the details. "Time(8)" is the generated description of the structure defined by SQLite3 based upon Data Type options. So I apologize if I don't understand you comment.

SQLite didn't generate "Time(3)".  Some other piece of software may have done so, and told SQLite to use it as a datatype.

SQLite doesn't have a Time datatype.  See the list of datatypes at the top of section 2 of

<https://www.sqlite.org/datatype3.html>

SQLite is taking "Time(8)" and understanding it as "TEXT".  See the table in section 3.1.1 of the above web page.

SQLite doesn't recognise limits to the number of characters in text fields.  SQLite is ignoring the "(8)" giving it a limit of 8 characters.  See the paragraph just above that table.

Hope this helps.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: My TimeStamp field keeps populating though I don't write anything to it

David Raymond
https://www.sqlite.org/datatype3.html#determination_of_column_affinity

"Time(8)" ends up as  numeric affinity for that field. When it's given the string "Thu Jan 01 00:00:00 EST 1970" to store in the numeric field, that string can't be losslessly converted to a number, and thus it leaves it alone and stores it as-given as text.



-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Simon Slavin
Sent: Tuesday, February 05, 2019 10:44 AM
To: SQLite mailing list
Subject: Re: [sqlite] My TimeStamp field keeps populating though I don't write anything to it

On 5 Feb 2019, at 3:29pm, Scott <[hidden email]> wrote:

> Thanks for the timely response. I provided below the details. "Time(8)" is the generated description of the structure defined by SQLite3 based upon Data Type options. So I apologize if I don't understand you comment.

SQLite didn't generate "Time(3)".  Some other piece of software may have done so, and told SQLite to use it as a datatype.

SQLite doesn't have a Time datatype.  See the list of datatypes at the top of section 2 of

<https://www.sqlite.org/datatype3.html>

SQLite is taking "Time(8)" and understanding it as "TEXT".  See the table in section 3.1.1 of the above web page.

SQLite doesn't recognise limits to the number of characters in text fields.  SQLite is ignoring the "(8)" giving it a limit of 8 characters.  See the paragraph just above that table.

Hope this helps.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: My TimeStamp field keeps populating though I don't write anything to it

Scott
In reply to this post by Simon Slavin-3

I think I understand now and I'm using SQLite Studio.  It appears SQLite Studio is acting as a enhanced wrapper around SQLite3.  I did some reading and there is a SQLite Studio manual with with a list of 16 data types and it looks as if you can configure editors around those data types. I probably need to be apart of that forum for this question.
Thanks for the help! I appreciate the patience until my thick head could be penetrated :)
16 (17) Data Types:https://github.com/pawelsalawa/sqlitestudio/wiki/User_Manual#value-editor-dialog


Scott ValleryEcclesiastes 4:9-10

    On Tuesday, February 5, 2019, 10:44:40 AM EST, Simon Slavin <[hidden email]> wrote:  
 
 On 5 Feb 2019, at 3:29pm, Scott <[hidden email]> wrote:

> Thanks for the timely response. I provided below the details. "Time(8)" is the generated description of the structure defined by SQLite3 based upon Data Type options. So I apologize if I don't understand you comment.

SQLite didn't generate "Time(3)".  Some other piece of software may have done so, and told SQLite to use it as a datatype.

SQLite doesn't have a Time datatype.  See the list of datatypes at the top of section 2 of

<https://www.sqlite.org/datatype3.html>

SQLite is taking "Time(8)" and understanding it as "TEXT".  See the table in section 3.1.1 of the above web page.

SQLite doesn't recognise limits to the number of characters in text fields.  SQLite is ignoring the "(8)" giving it a limit of 8 characters.  See the paragraph just above that table.

Hope this helps.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
 
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: My TimeStamp field keeps populating though I don't write anything to it

Scott
In reply to this post by David Raymond
Hi David!
I think I know what direction I need to go and this helps and makes sense. I may simply need to figure out what is sending the "Thu Jan 01 00:00:00 EST 1970" and handle it from there.
Thanks!
Scott ValleryEcclesiastes 4:9-10

    On Tuesday, February 5, 2019, 11:11:56 AM EST, David Raymond <[hidden email]> wrote:  
 
 https://www.sqlite.org/datatype3.html#determination_of_column_affinity

"Time(8)" ends up as  numeric affinity for that field. When it's given the string "Thu Jan 01 00:00:00 EST 1970" to store in the numeric field, that string can't be losslessly converted to a number, and thus it leaves it alone and stores it as-given as text.



-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Simon Slavin
Sent: Tuesday, February 05, 2019 10:44 AM
To: SQLite mailing list
Subject: Re: [sqlite] My TimeStamp field keeps populating though I don't write anything to it

On 5 Feb 2019, at 3:29pm, Scott <[hidden email]> wrote:

> Thanks for the timely response. I provided below the details. "Time(8)" is the generated description of the structure defined by SQLite3 based upon Data Type options. So I apologize if I don't understand you comment.

SQLite didn't generate "Time(3)".  Some other piece of software may have done so, and told SQLite to use it as a datatype.

SQLite doesn't have a Time datatype.  See the list of datatypes at the top of section 2 of

<https://www.sqlite.org/datatype3.html>

SQLite is taking "Time(8)" and understanding it as "TEXT".  See the table in section 3.1.1 of the above web page.

SQLite doesn't recognise limits to the number of characters in text fields.  SQLite is ignoring the "(8)" giving it a limit of 8 characters.  See the paragraph just above that table.

Hope this helps.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
 
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: My TimeStamp field keeps populating though I don't write anything to it

Simon Slavin-3
In reply to this post by David Raymond
On 5 Feb 2019, at 4:11pm, David Raymond <[hidden email]> wrote:

> "Time(8)" ends up as  numeric affinity for that field.

The table in 3.1.1 shows how SQLite recognises the type you supply and turns it into a type it can use.  The lowest row of the table shows that "DATETIME" is recognised as NUMERIC.  However, it lists "DATETIME" specifically, not "TIME".

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Email bounce

Marc L. Allen
In reply to this post by Scott

Everyone,

I apologize for the email bounce notifications. My company was acquired a few months back and they’re trying to move us to their email system, so they’re trying to support two email addresses.

I hope it’s only for the day, but if it becomes a burden, I can unsubscribe and come back when it’s done.

Thanks

Marc


Confidentiality notice: This e-mail is intended solely for use of the individual or entity to which it is addressed and may contain information that is proprietary, privileged, company confidential and/or exempt from disclosure under applicable law. If the reader is not the intended recipient or agent responsible for delivering the message to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify the sender by reply e-mail or collect telephone call and delete or destroy all copies of this e-mail message, any physical copies made of this e-mail message and/or any file attachment(s).
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: My TimeStamp field keeps populating though I don't write anything to it

Simon Slavin-3
In reply to this post by Scott
On 5 Feb 2019, at 4:45pm, Scott <[hidden email]> wrote:

> I think I understand now and I'm using SQLite Studio.  It appears SQLite Studio is acting as a enhanced wrapper around SQLite3.

That makes a lot more sense.  Well done for figuring it out.

You have lots of options on how to store your dates.  You can store them as text, or as a number of days.  Which one you do depends on whether you want to make searching easier, or counting days easier.

Good luck with your task.  And don't hesitate to come back if you think we can help with something.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: My TimeStamp field keeps populating though I don't write anything to it

Shawn Wagner
In reply to this post by Simon Slavin-3
Reread the rules at the start of 3.1. TIME doesn't have any of the
substrings used to indicate a particular other affinity, so it's treated as
NUMERIC.

On Tue, Feb 5, 2019, 8:53 AM Simon Slavin <[hidden email] wrote:

> On 5 Feb 2019, at 4:11pm, David Raymond <[hidden email]> wrote:
>
> > "Time(8)" ends up as  numeric affinity for that field.
>
> The table in 3.1.1 shows how SQLite recognises the type you supply and
> turns it into a type it can use.  The lowest row of the table shows that
> "DATETIME" is recognised as NUMERIC.  However, it lists "DATETIME"
> specifically, not "TIME".
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: My TimeStamp field keeps populating though I don't write anything to it

Scott
In reply to this post by Simon Slavin-3
Hi Simon...
Thanks, I do have my  moments! LOL! I have a Regex on the GUI limiting the user to hh:mm:ss format or simply blank. Somehow that must be getting translated into a full date and time as default when blank.
Scott ValleryEcclesiastes 4:9-10

    On Tuesday, February 5, 2019, 11:55:47 AM EST, Simon Slavin <[hidden email]> wrote:  
 
 On 5 Feb 2019, at 4:45pm, Scott <[hidden email]> wrote:

> I think I understand now and I'm using SQLite Studio.  It appears SQLite Studio is acting as a enhanced wrapper around SQLite3.

That makes a lot more sense.  Well done for figuring it out.

You have lots of options on how to store your dates.  You can store them as text, or as a number of days.  Which one you do depends on whether you want to make searching easier, or counting days easier.

Good luck with your task.  And don't hesitate to come back if you think we can help with something.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
 
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: My TimeStamp field keeps populating though I don't write anything to it

Simon Slavin-3
In reply to this post by Shawn Wagner
On 5 Feb 2019, at 4:58pm, Shawn Wagner <[hidden email]> wrote:

> Reread the rules at the start of 3.1. TIME doesn't have any of the
> substrings used to indicate a particular other affinity, so it's treated as NUMERIC.

You're right.  I somehow remembered the default as TEXT, not NUMERIC.  I was wrong.  Thanks for the correction.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: My TimeStamp field keeps populating though I don't write anything to it

Scott
Last reply... I figured out the cause. I had a Regex to validate but there was a second validation I forgot about with a DateTime object check causing the issue - so blank was defaulting. I removed it and no issue.
Thanks!
Scott ValleryEcclesiastes 4:9-10

    On Tuesday, February 5, 2019, 12:05:43 PM EST, Simon Slavin <[hidden email]> wrote:  
 
 On 5 Feb 2019, at 4:58pm, Shawn Wagner <[hidden email]> wrote:

> Reread the rules at the start of 3.1. TIME doesn't have any of the
> substrings used to indicate a particular other affinity, so it's treated as NUMERIC.

You're right.  I somehow remembered the default as TEXT, not NUMERIC.  I was wrong.  Thanks for the correction.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
 
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users