Convert LocalDate to TimeStamp with UTC

Question

I am taking LocalDate as input and want to convert it into this format to search in oracle DB.

input - "2010-10-10"

Output- 10-OCT-10 07.39.02.713000000 AM UTC

I tried using TimeStamp and DateTime but getting date in these formats respectively.

2020-10-10 00:00:00.0 
2020-10-10T00:00:00.000+05:30

I used

Timestamp.valueOf(startDate.atStartOfDay());
DateTime.parse(startDate.toString());

Can you please help me? Thank you in advance

Updated.

"I tried using TimeStamp and DateTime" - please show us the code that you've been using. — Jon Skeet, Jun 05 '21 at 17:56
Check documentation for DateTimeFormatter: https://docs.oracle.com/javase/8/docs/api/java/time/format/DateTimeFormatter.html — hamlet, Jun 05 '21 at 17:57
Are you looking for java solution or oracle. You can use `sys_extract_utc(to_timestamp('2010-10-10','YYYY-MM-DD')) utc_time` if you are using oracle sql. — Koushik Roy, Jun 05 '21 at 18:00
I can see that you have just edited your question. The same answer with the format, `dd-MMM-uu HH:mm:ss.SSSSSSSSS a zzz` will work for your updated question. — Arvind Kumar Avinash, Jun 05 '21 at 19:23

score 2 · Accepted Answer · answered Jun 05 '21 at 18:15

Parse the given string to LocalDate and convert it into ZonedDateTime using LocalDate#atStartOfDay(ZoneId).

import java.time.LocalDate;
import java.time.ZoneId;
import java.time.ZonedDateTime;
import java.time.format.DateTimeFormatter;
import java.util.Locale;

public class Testing {
    public static void main(String[] args) {
        LocalDate date = LocalDate.parse("2010-10-10");
        ZonedDateTime zdt = date.atStartOfDay(ZoneId.of("Etc/UTC"));
        System.out.println(zdt);

        // Custom format
        DateTimeFormatter dtf = DateTimeFormatter.ofPattern("uuuu-MM-dd HH:mm:ss.SSS a zzz", Locale.ENGLISH);
        System.out.println(dtf.format(zdt));
    }
}

Output:

2010-10-10T00:00Z[Etc/UTC]
2010-10-10 00:00:00.000 AM UTC

Learn more about java.time, the modern Date-Time API^* from Trail: Date Time.

^{* For any reason, if you have to stick to Java 6 or Java 7, you can use ThreeTen-Backport which backports most of the java.time functionality to Java 6 & 7. If you are working for an Android project and your Android API level is still not compliant with Java-8, check Java 8+ APIs available through desugaring and How to use ThreeTenABP in Android Project.}

@david use `DateTimeFormatter dtf = DateTimeFormatter.ofPattern("uuuu-MM-dd HH:mm:ss.S a zzz", Locale.ENGLISH);` format for your expected output. — Arunkumar Pushparaj, Jun 05 '21 at 18:22
@ArunkumarPushparaj - Yes, if he needs just one digit after the dot. I thought to change it but then left it thinking that he can adjust the number of `S`s as per his requirement. — Arvind Kumar Avinash, Jun 05 '21 at 18:24

score 0 · Answer 2 · answered Jun 05 '21 at 18:28

Don't pass date and timestamp to oracle as strings (varchar2), just use bind variables of required data types: oracle.sql.DATE or oracle.sql.TIMESTAMP.

You can convert(cast) them in Oracle. Also you can change timezones using SYS_EXTRACT_UTC, TO_UTC_TIMESTAMP_TZ or AT TIME ZONE

Convert LocalDate to TimeStamp with UTC

2 Answers2