Insert date and time using UTC time in a DATE column

[EM1107]

Good day Everyone.

I an new to Oracle PL/SQL and I am trying to insert a date including time in a DATE column.
When I select the SYSDATE option it insert the date but not the time.

How can I insert the system Date add the time on to the same column and the time should be in UTC time?.

Meaning that the time should be Universal time.

Can this be done or do I have to have a separate column for the time?

Thanks for you comments and your help;

 

[Andrzejek]

[tt]SELECT SYSDATE FROM DUAL[/tt]
gives me:
[tt]4/9/2020 10:30:03 AM[/tt], so SYSDATE does return the time portion

How is your field (where you want to store date and time) defined in the table?


---- Andy

There is a great need for a sarcasm font.

 

[EM1107]

The filed is just a date data type field and if I do an insert into the date field and select the SYSDATE it only appear as a date no time with it when I do a select.
If the SYSDATE does insert the date and time how can I convert the time provided by the SYSDATE into a UTC DATE?

 

[Andrzejek]

If you do this request (based on this place):[tt]

SELECT TO_CHAR(MydateField, 'DD-MON-YYYY HH24:MI:SSxFF') As NyDate
FROM MyTable
[/tt]
What do you get? Just Date, no Time portion?


---- Andy

There is a great need for a sarcasm font.

 

[EM1107]

If I run your command Andy I get the following error.

ORA-01821: date format not recognized
01821. 00000 - "date format not recognized"
*Cause:
*Action:

 

[EM1107]

Andy if I run the command select SYSDATE from DUAL I only get the date no TIME.
I only get DD-MM-YYYY.

 

[Andrzejek]

date format not recognized"

You may try something more generic: [tt]
SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') AS MyDate
FROM DUAL
[/tt]
And then:
[tt]
SELECT TO_CHAR(MyDateField, 'DD-MON-YYYY HH:MI:SS') AS MyDate
FROM MyTable[/tt]

"I only get the date no TIME" - see this


---- Andy

There is a great need for a sarcasm font.

 

[EM1107]

Cool that resolved the issue for SQL Developer not showing the proper date and Time. How bout getting my date inserted in UTC time?
For the moment if I insert the date time it will be current date time not UTC. How do I convert the time to UTC?

 

[Andrzejek]

Coordinated Universal Time (or UTC) is not adjusted for daylight saving time, but your time in Quebec Canada is changed twice a year. So, you can either subtract 4 hours from your time since Coordinated Universal Time is 4 hours ahead of QC, Canada, but you would have to adjust it when you change time. Or you can use some Web Service to calculate it for you.

Hopefully someone will jump in and offer a better solution

The [blue][tt]GETUTCDATE()[/tt][/blue] function returns the current database system UTC date and time, in a 'YYYY-MM-DD hh:mm:ss. mmm' format. Try that...

---- Andy

There is a great need for a sarcasm font.

 

[EM1107]

If I wanted to only modify the time a on time that would be what I would do. But If I want to add this as part of a trigger for instance, I do not want to have to modify the trigger twice a year just to be sure it insert the information with the correct Date and Time. Same goes if I have an application that work on UTC time.

 

[Andrzejek]

I edited my reply after your post... See above.


---- Andy

There is a great need for a sarcasm font.

 

[EM1107]

Thanks for you help guys but I found what I was looking for.
I can use the following select sys_EXTRACT_UTC(systimestamp) from dual;
This is returning the UTC DATE from the system date. I can just place this into a variable and insert it in my table.

I just created a variable as follow: v_UTCDATE DATE:=sys_EXTRACT_UTC(systimestamp);
and than I placed the variable in my insert query. It inserted the date using UTC time.

 

[Andrzejek]

guys"? I am just one person...

Do you really need this v_UTCDATE DATE variable? You may as well just do:
[tt]
INSERT INTO SomeTable(Field1, [blue]MyUTCDATE,[/blue] ...)
VALUES (123, [blue]sys_EXTRACT_UTC(systimestamp)[/blue], ...)[/tt]

Just my opinion....


---- Andy

There is a great need for a sarcasm font.

 

[EM1107]

Actually! I did not try it that way so it might be just as good. I will give it a try.

 

[EM1107]

You are correct it work just fine. Thanks.