[Solved] Modify timestamp via SQL and use in queries

Creating tables and queries
Post Reply
Doranwen
Posts: 83
Joined: Sun May 26, 2019 6:46 am

[Solved] Modify timestamp via SQL and use in queries

Post by Doranwen »

I'm working some more on my fanfic database (which is a hybrid db with HSQLDB 2.5.1.jar), and having generated some reports already - but having tons more records to add - I realized I wanted a way to limit which records I pull in queries by when they were added so I don't end up duplicating the information I share with others as a result. I've done enough research to know that adding a timestamp column that will automatically set the timestamp should be the following SQL:

Code: Select all

ALTER TABLE "Fanfics" ADD COLUMN "Timestamp" TIMESTAMP DEFAULT CURRENT_TIMESTAMP
(Though I'd like confirmation that that will only affect record *creation* , and not update the record with the current timestamp any time the record is modified. I only want it for creation, not updating. It should set it once and never modify it after that.)

But i have two minor things still to sort out. First, I'd like to set a different date for all of the records already in the database, as I'm not likely to remember today's date as well as, say, New Year's for this year (which is also valid for this purpose as I haven't added any new records to my main table since then, only modified them). I presume a single SQL command would work beautifully across all 960-ish records that I currently have.

Second, I need to be able to use the timestamp in my queries, so I can filter to only records added *after* a particular date, for instance. I don't care a whit about the time of day, only the date, but I gather that's not something I can turn off with timestamps, lol, but I'm likely to just be wanting the query to return any record added after a particular day of a particular year, often in combination with a much more complicated query.

A minor issue is adding a field to my form that will display the record creation timestamp for that record with only the date - and without being able to edit it - but that's low on my priority list, so if that's tricky, it can definitely wait.

And if there's a way to set the timestamp as only a date and skip the entire time of day part of things, I'd be happy to do that too, since I only need the date filtering and not time of day.

I'm wading through search results for SQL sites and forum threads and not finding anything that quite answers my questions, but it's possible I've missed something due to not knowing what to search for.
Last edited by Doranwen on Mon May 10, 2021 6:24 am, edited 1 time in total.
LibreOffice 5.1.6.2 on Linux Mint 18.1
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Modify timestamp via SQL and use in queries

Post by Villeroy »

You can define 2 separate columns for time and date.
Untested script:

Code: Select all

ALTER TABLE "Fanfics" ADD COLUMN "D" DATE DEFAULT CURRENT_DATE;
ALTER TABLE "Fanfics" ADD COLUMN "T" TIME DEFAULT CURRENT_TIME;
UPDATE "Fanfics" SET D = CAST("Timestamp" AS DATE);
UPDATE "Fanfics" SET T = CAST("Timestamp" AS TIME);
ALTER TABLE "Fanfics" DROP COLUMN "Timestamp";
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Doranwen
Posts: 83
Joined: Sun May 26, 2019 6:46 am

Re: Modify timestamp via SQL and use in queries

Post by Doranwen »

Well, since I haven't actually created the timestamp column yet, I should be able to just add the column with the current_date and not do anything else, right?

That solves the "I don't need the time of day" bit, then. Just have to figure out how to modify that for the records I already have in the table, and then modify a query to limit based on whether it was created after a particular date or not.
LibreOffice 5.1.6.2 on Linux Mint 18.1
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Modify timestamp via SQL and use in queries

Post by Villeroy »

SELECT * FROM "Fanfics" WHERE D >= :Since_When
SELECT * FROM "Fanfics" WHERE CAST("Timestamp" AS DATE) >= :Since_When
SELECT * FROM "Fanfics" WHERE DATEDIFF('dd', "Timestamp", CURRENT_TIMESTAMP) >= :How_many_Days
SELECT * FROM "Fanfics" WHERE DATEDIFF('dd', "D", CURRENT_DATE) >= :How_many_Days
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
chrisb
Posts: 298
Joined: Mon Jun 07, 2010 4:16 pm

Re: Modify timestamp via SQL and use in queries

Post by chrisb »

hello Doranwen,

i have a suggestion:

Code: Select all

alter table "Fanfics" add column "DateStamp" date default current_date;
creates a date field & sets every record to current date.

i think that we can more or less assume that the greater the value of "FanficID" the more recent the record.
now if you execute the code below the field "DateStamp" will be updated with a unique value, the most recent record with '2020-12-31' & subsequent records will be equal to the previous records value minus one day.
you should back up your table before executing from menu:Tools>SQL:

Code: Select all

update "Fanfics" f1 set "DateStamp" =
dateadd
('day', -
	(
		select rownum from
		(
			select "FanficID", rownum()
			from
				(select "FanficID" from "Fanfics" order by "FanficID" desc)
		) f2
		where f1."FanficID" = f2."FanficID"
	),
	date '2021-01-01'
);
open office 4.1.14 & LibreOffice 6.4.4.2 x64 using HSQL 1.8.0.10 (Embedded) and HSQL 2.6.0 (Split) on Windows 10
Doranwen
Posts: 83
Joined: Sun May 26, 2019 6:46 am

Re: Modify timestamp via SQL and use in queries

Post by Doranwen »

I've got the field created and updated with 2021-01-01 as the date (since it might display as 01/01/21 but underneath it's actually saving it as YYYY-MM-DD). I'll have to add some more records and then test the query filter option. Currently I've been doing a pass through as I added a few new tables and am separating out major vs. minor characters and ships. I've got a few hundred more to finish editing, and then I can test it. But I think I've got some good solutions here, so thank you all very much!

The update SQL is very simple, but I'll put it here in case someone new to databases finds it useful. With the table Fanfics and a field called Date, this is the command to set the date for all records currently in the table (but doesn't affect the default value which is populated by whatever the current date is):

Code: Select all

UPDATE "Fanfics" SET "Date" = '2021-01-01'
LibreOffice 5.1.6.2 on Linux Mint 18.1
Post Reply