Date/Timestamp to Record When a Record Was Added to the Table

Date / Timestamp to record when a record was added to the table?

You can create a non-nullable DATETIME column on your table, and create a DEFAULT constraint on it to auto populate when a row is added.

e.g.

CREATE TABLE Example
(
SomeField INTEGER,
DateCreated DATETIME NOT NULL DEFAULT(GETDATE())
)

Column to create timestamp of when a record was added

Use a default value:

create table t (
     . . . ,
     createdAt datetime default getdate()
);

If you don't provide a value on insert, the value is given the default value -- and it would only change after that if you did an update.

Updating a Table Based on a Timestamp Value in the Table

You can:

1. Create an event on your database and run UPDATE query.

or

2. Create a CRON job to run specific PHP file on your server where will be your UPDATE query.

And set them to run e.g. for every day.

Adding time stamp values to a table

As there's no TIME datatype in Oracle, and as you need to collect information up to minutes (you certainly don't need fractional seconds; do you?), use DATE datatype as it contains both date and time information.

Also - although your task says that you do need it - you don't need date of departure; it is contained in time of departure.

Something like this:

SQL> CREATE TABLE train
  2  (
  3     train_no          INT,
  4     time_of_dep       DATE,
  5     time_of_arrival   DATE
  6  );

Table created.

Insert a row; note that it is a good habit to specify all columns you're inserting into. You can add minutes the way you tried to, but - why wouldn't you use interval? It is easier to understand what you're doing:

SQL> INSERT INTO train (train_no, time_of_dep, time_of_arrival)
  2          VALUES (5,
  3                  SYSDATE + INTERVAL '33' MINUTE,
  4                  SYSDATE + INTERVAL '115' MINUTE);

1 row created.

OK, so - what's being inserted?

SQL> SELECT SYSDATE, t.*
  2    FROM train t;

SYSDATE    TRAIN_NO TIME_OF_ TIME_OF_
-------- ---------- -------- --------
11.02.22          5 11.02.22 11.02.22

Whoops! Not very useful. So - modify date format:

SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi';

Session altered.

SQL> SELECT SYSDATE, t.*
  2    FROM train t;

SYSDATE            TRAIN_NO TIME_OF_DEP      TIME_OF_ARRIVAL
---------------- ---------- ---------------- ----------------
11.02.2022 08:33          5 11.02.2022 09:05 11.02.2022 10:27

SQL>

That looks better.

Alternatively, you could have used TO_CHAR function with desired format mask:

SQL> select train_no,
  2    to_char(time_of_dep    , 'dd.mm.yyyy hh24:mi') dep,
  3    to_char(time_of_arrival, 'dd.mm.yyyy hh24:mi') arr
  4  from train;

  TRAIN_NO DEP              ARR
---------- ---------------- ----------------
         5 11.02.2022 09:05 11.02.2022 10:27

SQL>

---

[EDIT] If it must be a timestamp, you'd do the same:

SQL> CREATE TABLE train
  2  (
  3     train_no          INT,
  4     time_of_dep       timestamp,
  5     time_of_arrival   timestamp
  6  );

Table created.

SQL> INSERT INTO train (train_no, time_of_dep, time_of_arrival)
  2          VALUES (5,
  3                  systimestamp + INTERVAL '33' MINUTE,
  4                  systimestamp + INTERVAL '115' MINUTE);

1 row created.

SQL> SELECT SYSDATE, t.*
  2    FROM train t;

SYSDATE            TRAIN_NO TIME_OF_DEP               TIME_OF_ARRIVAL
---------------- ---------- ------------------------- -------------------------
11.02.2022 08:57          5 11.02.22 09:30:35,783378  11.02.22 10:52:35,783378

SQL>

Select the last to a certain date record from the dependent table

In SQL Server, we can use a row-limiting lateral join to bring the latest record in the second table prior to the timestamp of the first table:

select a.card_number, b.*
from table_a a
cross apply (
    select top (1) b.*
    from table_b b 
    where b.card_number = a.card_number and b.timestamp < a.reissue_date 
    order by b.timestamp desc
) b

This also eliminates rows that have no match, which is consistent with your data.

How to create mysql table with column timestamp default current_date?

Use CURRENT_TIMESTAMP function instead of CURRENT_DATE() function

Try this:

DROP TABLE IF EXISTS `visitors`;
CREATE TABLE `visitors` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `ip` VARCHAR(32) NOT NULL,
  `browser` VARCHAR(500) NOT NULL,
  `version` VARCHAR(500) NOT NULL,
  `platform` ENUM('w','l','m') NOT NULL,
  `date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `person` (`ip`,`date`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

Should I use the datetime or timestamp data type in MySQL?

Timestamps in MySQL are generally used to track changes to records, and are often updated every time the record is changed. If you want to store a specific value you should use a datetime field.

If you meant that you want to decide between using a UNIX timestamp or a native MySQL datetime field, go with the native DATETIME format. You can do calculations within MySQL that way
("SELECT DATE_ADD(my_datetime, INTERVAL 1 DAY)") and it is simple to change the format of the value to a UNIX timestamp ("SELECT UNIX_TIMESTAMP(my_datetime)") when you query the record if you want to operate on it with PHP.

---

Related Topics