-1

I want to insert a default Unix date time in database table. But i can't able to insert this. Please help me to set the default Unix date time.Am new for Oracle.

user1804985
  • 297
  • 1
  • 7
  • 18
  • Why do you want to store a Unix date instead of a proper database `DATE` or `TIMESTAMP` data type? – eaolson Dec 01 '12 at 04:37
  • Some one told. It's very useful to search the data quickly. That's why i need this.Because my table have more than crore data. – user1804985 Dec 01 '12 at 04:48
  • @user1804985 - Declaring the field as a `NUMBER` and inserting the Unix timestamp doesn't do anything to allow you to search the data quickly. It's at least as easy to search data stored in a proper `DATE` column particularly since that's what all of Oracles date/ time functions can handle. – Justin Cave Dec 01 '12 at 05:07
  • Did this question just become a duplicate when you asked this very similar question: http://stackoverflow.com/questions/13656520/create-trigger-for-auto-incerment-id-and-default-unix-datetime ? Or is there a difference that I'm missing? – Justin Cave Dec 01 '12 at 05:17

2 Answers2

0

Are you looking for SYSTIMESTAMP e.g. below?

  INSERT INT TABLE(A, B, DATETIMECOL) 
  VALUES ('a', 'b', SYSTIMESTAMP);

This will insert current time stamp of the server in the database.

Yogendra Singh
  • 33,927
  • 6
  • 63
  • 73
  • I need like as getdate() function in sql. – user1804985 Dec 01 '12 at 04:01
  • What do you mean? Create a function with query as: `SELECT SYSTIMESTAMP FROM DUAL`, this will return the current time stamp. – Yogendra Singh Dec 01 '12 at 04:02
  • Suppose i would like to insert a row means the date field inserted automatically in Unix date time format. – user1804985 Dec 01 '12 at 04:06
  • My table have three field fld_id, fld_date,fld_value. Now i insert the value means the date field automatically inserted in Unix date format. – user1804985 Dec 01 '12 at 04:07
  • @user1804985 Update your table definition and make `fld_date` to use default as `SYSTIMESTAMP`. **If your column `fld_date` is defined as date time then don't worry about the format at time of insertion.** You need to apply the format at the time of retrieval. – Yogendra Singh Dec 01 '12 at 04:10
  • No, I want to insert default Unix date time. Current datetime is '14/03/2010 08:16' means my date field column value like this 1268536560.. – user1804985 Dec 01 '12 at 04:19
  • @user1804985 In that case update the default value as `(sysdate - to_date('01-JAN-1970','DD-MON-YYYY')) * (86400)` – Yogendra Singh Dec 01 '12 at 04:33
0

SYSDATE returns the current day and time as an Oracle DATE (an Oracle DATE always contains both a day and a time component to the second).

SYSTIMESTAMP returns the current day and time as an Oracle TIMESTAMP WITH TIME ZONE which includes both fractional seconds and a time zone.

If you want to insert a NUMBER that represents the number of seconds since January 1, 1970 (which I'm assuming is what you mean by "Unix date time format", that would be (SYSDATE - date '1970-01-01') * 60 * 60 * 24.

Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • Yes, You are right. I want to insert this Unix value to my date field automatically when i insert a data. – user1804985 Dec 01 '12 at 04:22
  • @user1804985 - If the column is defined as a `DATE`, you cannot insert a numeric value. If you want to store a numeric value, the column must be declared as a `NUMBER`. – Justin Cave Dec 01 '12 at 04:36
  • I declare the datatype for that column name is number. – user1804985 Dec 01 '12 at 04:48
  • @user1804985 - Then, as I said, you can insert the result of `(SYSDATE - date '1970-01-01') * 60 * 60 * 24` into the `NUMBER` column. – Justin Cave Dec 01 '12 at 04:52
  • K thank you. But it inserted automatically when i insert a record without date field. – user1804985 Dec 01 '12 at 04:54
  • @user1804985 - Sorry, I'm not sure what you are saying. Do you have a problem? If so, please explain exactly what the problem is. What behavior are you seeing? What behavior do you want? What is your code? – Justin Cave Dec 01 '12 at 04:59