CODEWITHSUNDEEP
mysqlsql
user3880721
user3880721

Reputation: 613

Current time on MySQL

I have a database table. One of the columns, say column Time_NOW is of type TIME. if Time_NOW is null, i need that column entry be the current-time-- whenever that entry is looked up.

I tried the entry now() in column Time_NOW for the null values. However, it is setting its value for good to the current-time at the time of insertion to that row. what i need is the current-time at the time of reading that value from that row.

How can this be done?

I'm using MySQL.

TIA

Upvotes: 0

Views: 340

Answers (5)

spencer7593
spencer7593

Reputation: 108460

There's no way to modify the definition of the column Time_NOW to get the behavior you described. To have a SQL query perform the specified operation (as I understand it), you would actually need to modify the query.

Your table definition remains as it is, with the column being nullable, e.g.

 `Time_NOW` TIME DEFAULT NULL

Your SELECT statement that "accesses" this column would need to retrieve the value from the column, test if that value is NULL, and either return the value of the column when it's not NULL, or return a different expression when the column is NULL.

Since the Time_NOW column is defined with a datatype of TIME, you'd almost certainly want the replacement value to be of the same datatype, so we want that alternate expression to return TIME (not DATETIME, not TIMESTAMP, etc.)

The expression TIME(NOW()) will return the current time (HH:MM:SS), according to the server clock, and the timezone of the client. (This isn't the only expression that will return the current time; there are other expressions that will return the same value.)

The conditional test (for Time_NOW being NULL or not) can be done is several different ways. As examples, using an ANSI-standard CASE expression, e.g.:

SELECT CASE WHEN t.Time_NOW IS NULL THEN TIME(NOW()) ELSE t.Time_NOW END AS Time_NOW 
  FROM mytable t

or, using a MySQL IF() function:

SELECT IF(t.Time_NOW IS NOT NULL,t.Time_NOW,TIME(NOW())) AS Time_NOW

or, using the ISNULL() function:

SELECT IFNULL(t.Time_NOW,TIME(NOW())) AS Time_NOW

or using the COALESCE() function:

SELECT COALESCE(t.Time_NOW,TIME(NOW())) AS Time_NOW

Upvotes: 1

Vishal Suthar
Vishal Suthar

Reputation: 17194

You can use CURRENT_TIMESTAMP:

Select COALESCE(Time_NOW, CURRENT_TIMESTAMP)

Here is the sample demo: SQL Fiddle

Upvotes: 0

juergen d
juergen d

Reputation: 204854

Use

Current_time

Instead of

Now()

Like this

Select coalesce(time_now,current_time) as time_now
From your_table

Upvotes: 1

Sathish
Sathish

Reputation: 4487

try

 create table test (ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP)

Upvotes: 0

ic3b3rg
ic3b3rg

Reputation: 14927

Try SELECT COALESCE(Time_NOW, now())

Upvotes: 0

Related Questions