Oracle SQL developer timestamp format not changing to 24-CodePudding

I'm inserting a timestamp value using python into an oracle database

timestamp_v = datetime.utcnow().strftime("%d-%b-%Y %H:%M:%S")
print(timestamp_v)

cur = conn.cursor()
query = 'INSERT INTO all_machines_data(timestamp, hostname, ip_address, up_time, cpu_utilization, running_processes, total_memory, available_memory, used_memory, total_disk_capacity, free_disk_space, used_disk_space, network_requests_sent, network_requests_received) VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14)'
values = (timestamp_v, system_info()[1], system_info()[3], str(system_info()[2]), cpu_info()[0], cpu_info()[1], memory_info()[0], memory_info()[1], memory_info()[2], disk_info()[0], disk_info()[1], disk_info()[2], network_info()[0], network_info()[1])
cur.execute(query,values)

conn.commit()
print("Data Sent!")

>> 04-Apr-2022 23:30:22
>> Data Sent!

My problem is in sql developer not accepting to show it in 24h format and always showing the 12h format

I tried every solution I found on the internet and still no luck1- tried setting the correct format I want in Tools->Preferences->Database->NLS : DD-MON-YYYY HH24:MI:SS

- Tried using the query

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

- Tried restarting sql developer/ stopping oracles services and still no luck
any help is apperciated, Thanks!

CodePudding user response：


ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF';

SELECT SYSTIMESTAMP FROM DUAL;

SYSTIMESTAMP         
----------------------
2022-04-04 20:27:11.35 
1526000

CodePudding user response：

if I use ("%d-%b-%Y %H:%M:%S") in which the 24h format is %H I'm getting an error Failed to insert record into table ORA-01849: hour must be between 1 and 12 ,

You are relying on implicit conversion of your string to a timestamp, which uses your session NLS settings. Note that is your session from Python, which is separate from your SQL Developer session - and they have different NLS settings.

Change your insert to expect a string with a 24-hour time; instead of the first bind being simply

query="... values(:1, ..."
values = (timestamp_v, ...

make it:

query="... values(to_timestamp(:1, 'DD-MON-YYYY HH24:MI:SS`), ..."
values = (timestamp_v, ...

... which still replies on the month abbreviation being converted - implying your Python and Oracle sessions are using the same date language; you can specify the language on the Oracle side but... it would be better to use an unambiguous format like

timestamp_v = datetime.utcnow().strftime("%Y-%m-%d %H:%M:%S")
...
... values(to_timestamp(:1, 'YYYY-MM-DD HH24:MI:SS`), ...
values = (timestamp_v, ...

But it would be even better to bind the actual timestamp value and not use a string at all. I don't know Python but perhaps something as simple as:

query="... values(:1, ..."
values = (datetime.utcnow(), ...

(This answer suggests that is the case.)

It's much better to use the correct data type rather than converting to and from strings, whether that is implicit or explicit.

if I use ("%d-%b-%Y %I:%M:%S") with %I is for the 12h format, it's getting inserted no problems.

Yes, because you're now providing the hour as 11, not 23.

note that my sql developer is always showing the 12H format

No, it's showing 24-hour format, your time is just actually 11 am.