DATE field in SQL – Custom formats and timestamped entries

In this blog, I would be talking about the DATE field in SQL tables and will show some formatting masks and creating a timestamp entry for a row in Oracle. This is a table that I have created with two columns, one of the type DATE and another of the type VARCHAR.

The specialty of DATE field in Oracle is that DATE is basically a combination of DATE and TIME. This would be evident in subsequent snapshots, but this is interesting from an implementation perspective.

I now insert the SYSDATE after casting it as DD-MM-YYYY format into the DATE_DEMO table as shown in the below snapshot. 

I was trying to have an entry like “01-10-2016, First entry” by running the above insert statement.

The contents of the table after running the insert statement is as shown in the next snapshot.  

Now, the next query will choose the SYSDATE from the DUAL table. This is as shown in the next snapshot.

I cast the SYSDATE as “DD-MM-YY” and the output of SYSDATE from DUAL table is as shown in the below snapshot. 

Now inserting the data after casting SYSDATE in the DD-MM-YY format is as shown in the below queries. Also shown is the content of the table after running the insert statement.

Although we have inserted the SYSDATE into the table, observe that the TIME field is still mapped to 00:00:00 0. This is because we have not specified the format mask for the time field while inserting the record.

Now, we try to insert the SYSDATE directly into the table as shown in the below snapshot. 

The contents of the table after updating the SYSDATE is as shown in the below snapshot.   

The CURRENT_TIMESTAMP returns the current date and time in the session time zone, in a value of datatype TIMESTAMP WITH TIME ZONE. The select query for the same is as shown in the below snapshot.  

Now, let us see how we can use the timestamp field for log entries. Log entries have some characteristic features like they are incremental, never updated and show the state of a system in terms of operation being performed or errors encountered at some point of time. So how do we go about designing a table that captures these metrics?

In the below snapshot, I am creating a table called DATE_DEMO with two fields, namely, DATEINFO which has the datatype TIMESTAMP and INFO of the type VARCHAR.

The DATEINFO field has a default value set to CURRENT_TIMESTAMP. This means that whenever we insert a record into the table without referring to the DATEINFO field, it will automatically populate it with the CURRENT_TIMESTAMP. 


The next snapshot shows the table immediately after creation with no entries being populated yet. 

I now run three insert statements with some time delay as shown in the below snapshot.

Observe that in the above queries I am only populating the INFO field with text. I am letting Oracle handle populating the DATEINFO field for me.

 
The contents of the table after running a SELECT statement is as shown below.

 

Observe that the timestamps are increasing based on the system time. This is one way to design a logger table for an application in Oracle.