0

I'm new to PostgreSQL, and new to DB administration - please excuse a naive understanding of terms. Searching for answers to this question has proven difficult - if anyone can suggest better wording please advise.

I have a table containing historical and ongoing experiments that I would like to query for assessing past and present performance. I've been so far keeping this table in Excel and now as it's growing past Excel's usefulness I want to add it into my database.

Essentially, the columns of the table are Experiment_Id (string), StartDateTIme (datetime), and EndDateTime (datetime). There are some experiments that are still ongoing, so in my Excel worksheet there are rows with EndDateTime values of =Today(). The actual timestamp during the day is not important - these are tests which run for weeks and I can live with a timestamp of midnight.

I have read that PostgreSQL has a now() function, but I've only read of it being used in queries, not as values in tables. Is what I'm asking for possible in PostgreSQL?


I realize that a workaround to this problem could be to have a separate table containing a single column of Experiment_id and either a binary key of 'yes/no' is currently running. This would then be a massive list of zeros/falsies and with a few ones/truthies littered at the bottom.

The downside of this workaround is that then two tables would need to be updated when an experiment is completed instead of just one, and this could cause more errors. These tables are being updated manually.

1 Answer 1

3

You can use now(), CURRENT_DATE and their friends wherever an expresison is accepted. That means, you can define your table like

CREATE TABLE experiment (
    e_id text PRIMARY KEY,
    e_started date NOT NULL DEFAULT CURRENT_DATE,
    e_ended date
);

Here, when you insert an experiment, it is enough to insert its ID:

INSERT INTO experiment (e_id) VALUES ('this_experiment_about_AAAAA');

Still, the start date will be the day when this INSERT was issued. The end date is left to NULL - this is the usual way of having undefined values. You will find the running experiments like

SELECT e_id, e_started FROM experiments WHERE e_ended IS NULL;

When one finishes, you update the given row and set the end date as necessary (to CURRENT_DATE, for example).

Notes:

  • I used different column names than you. I'd advise to use snake_case instead of CamelCase - the latter often leads to confusion.
  • another naming consideration is that the table defines the data type, no need to reflect this in the column name.
  • the last naming thing: I prefixed the column names with the table name acronym. You don't have to do this, it's a matter of taste more than anything else.
1
  • Thanks for the answer - I could indeed just consider all e_ended values equal to NULL as currently running experiments. I'll also consider your notes, thanks for taking the time to mention them.
    – cbcoutinho
    Dec 13, 2017 at 12:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.