CODEWITHSUNDEEP
sqloracle-databasetimestampto-date
Arihant
Arihant

Reputation: 4047

SQL timestamp insert adding additional zeros

I have created the following table --

CREATE TABLE placed_order
(
    bill_number NUMBER(6) NOT NULL,
    customer_id NUMBER(6),
    order_time TIMESTAMP,
    paymentid NUMBER(6),
    total_bill_amount NUMBER(10,2),
    scheduled_delivery_time TIMESTAMP,
    address_id NUMBER(6),
    PRIMARY KEY (bill_number),
    FOREIGN KEY (customer_id) REFERENCES customer(customer_id), 
    FOREIGN KEY (paymentid) REFERENCES payment_information(paymentid),
    FOREIGN KEY (address_id) REFERENCES delivery_address(address_id)
);

When I insert the following values to the table --

INSERT INTO placed_order 
VALUES (123456, 1, '01-Feb-21 12:00:00 AM', 1, 34.56, '03-Feb-21 12:00:00 PM', 1);

The time is stored in the following format --

123456  1   01-FEB-21 12.00.00.000000000 AM 1   34.56   03-FEB-21 12.00.00.000000000 PM 1

This is causing issues when I am trying to run statements to do manipulations on the SQL such as average. For example, even if I want to convert that to a date using to_date --

select (TO_DATE(actual_delivery_time)) from delivery;

I get the following error --

ORA-01830: date format picture ends before converting entire input string
01830. 00000 - "date format picture ends before converting entire input string"

I tried checking the timestamp format in the nls_database_parameters which comes out to the following -- NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM

How can I figure out what the additional zeros are and why are they being appended?

Upvotes: 0

Views: 1198

Answers (1)

MT0
MT0

Reputation: 168232

A TIMESTAMP is a binary data format consisting of 7-20 bytes (century, year-of-century, month, day, hour, minute, second, up to 6 bytes for fractional seconds and up to 7 bytes for time zone information); it does NOT have a format.

'01-Feb-21 12:00:00 AM' is NOT a TIMESTAMP data-type; it is a string-literal and you are relying on an implicit cast from a string to a timestamp. Oracle will do this and your query is effectively:

INSERT INTO placed_order
VALUES (
  123456
  1,
  TO_TIMESTAMP(
    '01-Feb-21 12:00:00 AM',
    ( SELECT value FROM NLS_SESSION_PARAMETERS WHERE parameter = 'NLS_TIMESTAMP_FORMAT' )
  ),
  1,
  34.56,
  TO_TIMESTAMP(
    '03-Feb-21 12:00:00 PM',
    ( SELECT value FROM NLS_SESSION_PARAMETERS WHERE parameter = 'NLS_TIMESTAMP_FORMAT' )
  ),
  1
);

However, if NLS_TIMESTAMP_FORMAT does not match the format of your string then the conversion will fail.

How should the query be written?

You should either:

  • Explicitly use TO_TIMESTAMP( '01-Feb-21 12:00:00 AM', 'DD-MON-RR HH12:MI:SS AM' ) rather than relying on an implicit cast
  • Use a timestamp literal TIMESTAMP '2021-02-01 00:00:00'; or
  • Use a date literal DATE '2021-02-01'.

For example:

INSERT INTO placed_order (
  bill_number,
  customer_id,
  order_time,
  paymentid,
  total_bill_amount,
  scheduled_delivery_time,
  address_id
) VALUES (
  123456
  1,
  TIMESTAMP '2021-02-01 00:00:00',
  1,
  34.56,
  TIMESTAMP '2021-02-03 12:00:00',
  1
);

How can I format the TIMESTAMP value?

If you then want to display the timestamp with a format (remember, a TIMESTAMP is not stored with any format) then you want to use TO_CHAR to convert it to a string where it can have a format.

If you want to format the TIMESTAMP as a YYYY-MM-DD string then use:

SELECT TO_CHAR(actual_delivery_time, 'YYYY-MM-DD') FROM delivery

For example, even if I want to convert that to a date using to_date

TO_DATE does not convert a TIMESTAMP to a DATE; it converts a string to a DATE. If you want to cast the TIMESTAMP to a DATE data-type then use:

SELECT CAST(actual_delivery_time AS DATE) FROM delivery

(Note: A DATE data-type is also a binary data-type and is not stored with any format; however, the user-interface you are using to access the database may choose to display a date using the NLS_DATE_FORMAT session parameter.)

Why are fractional seconds being displayed?

I tried checking the timestamp format in the nls_database_parameters which comes out to the following -- NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM

Can anyone please help in figuring out what the additional zeros are and why are they being appended?

You are displaying a binary data-type; the user interface you are using is trying to be helpful and rather than displaying the bytes (which humans may find difficult to interpret) it is implicitly converting the binary data to a formatted string using the NLS_TIMESTAMP_FORMAT.

Given that your NLS_TIMESTAMP_FORMAT is DD-MON-RR HH.MI.SSXFF AM then the FF format model will display the fractional seconds; in this case your fractional seconds are 0 and it will display that up to the maximum precision of the timestamp.

Change the NLS_TIMESTAMP_FORMAT and it will change how (some) user interfaces display the TIMESTAMP (however, it does NOT change how the data is stored).

Can it be stored without fractional seconds?

If you want to change how the data is stored so it does not have fractional seconds then either:

  • Give the TIMESTAMP data-type you are using an explicit precision for the fractional seconds and use TIMESTAMP(0); or
  • Use a DATE data-type.

Both those data-types will store the value using 7-bytes and will store century, year-of-century, month, day, hour, minute and second. There is little difference between the two; one difference would be that the timestamp would be implicitly formatted using the NLS_TIMESTAMP_FORMAT and the date using the NLS_DATE_FORMAT session parameter.

Upvotes: 2

Related Questions