Trouble Converting Dates String to Yyyy-Mm-Ddthh:Mm:Ss-Hh:Mm Format

Trouble converting dates string to YYYY-MM-DDTHH:MM:SS-HH:MM format

Google Apps Script (GAS) has a built-in utility method you can leverage to format dates:

Utilities.formatDate()

Its based on Java's SimpleDateFormat class.

To format the date to meet your requirements the following should suffice:

var date = new Date("Mon Jun 30 2014 00:00:00 GMT-0400 (EDT)");

var formattedDate = Utilities.formatDate(
        date, 
        Session.getScriptTimeZone(), 
        "yyyy-MM-dd'T'HH:mm:ssXXX"
    );

Note: You may need to set your script's timezone from the GAS GUI menu via:

File->Project Properties->Info

Why I can't parse this date format yyyy-MM-dd'T'HH:mm:ss.SSSZ?

You should use Z the same way you use T for the parser to recognize the character in format

val stringDate = "2021-12-16T16:42:00.000Z"
val sdf = SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSS'Z'")
var consultationDate = sdf.parse(stringDate)

Change date from MM/dd to yyyy-MM-dd'T'HH:mm:ss.SSZ in Java

In the question, the date format pattern indicates a desire for 2-digit fractional seconds. SimpleDateFormat cannot do that.

The newer Java 8 Time API can, and you should be using that anyway.

If you're running on Java 6 or 7, get the ThreeTen-Backport library.

To parse a MM/dd formatted string and get a full timestamp with current year and time-of-day, in the default time zone, use the following code:

String strDate = "06/05";
MonthDay monthDay = MonthDay.parse(strDate, DateTimeFormatter.ofPattern("MM/dd"));
ZonedDateTime date = ZonedDateTime.now().with(monthDay);
System.out.println(date.format(DateTimeFormatter.ofPattern("uuuu-MM-dd'T'HH:mm:ss.SSZ")));

Sample Output

2020-06-05T14:52:48.45-0400

How to convert String to Date "yyyy-MM-dd'T'HH:mm:ss.SSS'Z'" in java

You need to start by converting the String into a more "malleable" format - something which can represent the date/time in a way through which you can generate different formats based on your need

Since it's 2020, you should start with the java.time.* API.

String input = "2020-02-21 16:36:30.072";
DateTimeFormatter inputFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss.SSS");
LocalDateTime ldt = LocalDateTime.parse(input, inputFormatter);

Ok, now, part of your requirement is to have the time zone, so you'll need to convert the LocalDateTime to a ZonedDateTime, you could, technically, do this in a single step, but it's a good demonstration

ZonedDateTime zdt = ldt.atZone(ZoneId.systemDefault());

DateTimeFormatter outputFormatter = DateTimeFormatter.ISO_OFFSET_DATE_TIME;
String output = outputFormatter.format(zdt);

System.out.println(input);
System.out.println(output);

This will output

2020-02-21 16:36:30.072
2020-02-21T16:36:30.072+11:00

I live in Australia, so my time zone is +10 (and +1 for daylight savings). You can specify a specific time zone if you wish, this is just for demonstration (and I couldn't be bothered trying to figure out 530+ time zone ;))

Wrong value for hour, minutes and mills when parsing date string to "yyyy-MM-dd'T'HH:mm:ss'-'hh:mm"

If you not need "-04:00" zone offset (timezone) then just truncate string "2015-04-13T10:17:00-04:00" to "2015-04-13T10:17:00"

        String fullDate = "2015-04-13T10:17:00-04:00";
        String truncatedDate = fullDate.substring(0, fullDate.lastIndexOf('-'));

        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss",
                Locale.ENGLISH);
        try {

            Date dte = format.parse(truncatedDate);
            System.out.println("date=" + dte);
        } catch (ParseException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

Result is:

date=Mon Apr 13 10:17:00 CEST 2015

Converting string date to string in yyyy-MM-dd'T'HH:mm:ss.SSSSSS format in java 7

As others have already mentioned, your requirement does not fit the use of Date and SimpleDateFormat since these only support milliseconds, that is, three decimals on the seconds, where you have six decimals (microseconds). So we need to find some other way. This is basically a good idea anyway, since Date and SimpleDateFormat are long outdated, and today we have better tools for the job.

I have got two suggestions for you.

java.time

Even in Java 7 I think that it's a good idea to use the modern Java date and time API that came out with Java 8, AKA JSR-310. Can you do that? Certainly; use the backport for Java 6 and 7, ThreeTen Backport. The modern API supports anything from 0 through 9 decimals on the seconds, and the code is straightforward when you know how:

private static DateTimeFormatter inputParser 
        = DateTimeFormatter.ofPattern("yyyy-MM-dd-HH.mm.ss.SSSSSS");
private static DateTimeFormatter outputFormatter 
        = DateTimeFormatter.ofPattern("yyyy-MM-dd'T'HH:mm:ss.SSSSSS");

public static String convertDate(String strDate) {
    return LocalDateTime.parse(strDate, inputParser)
            .format(outputFormatter);
}

I am using your own two format pattern strings. convertDate("2017-08-23-11.19.02.234850") returns 2017-08-23T11:19:02.234850.

There is a simplification possible: Since the format you want to obtain, conforms with ISO 8601, you don't need an explicit formatter for it. The modern classes understand and produce ISO 8601 formats natively, so you may use:

    return LocalDateTime.parse(strDate, inputParser).toString();

However, if the decimals on the seconds happened to end in 000, this will not print the last three zeroes. So if six decimals are required even in this case, use the formatter.

Regular expression

If you don't want to rely on an external library, even temporarily until once you upgrade to Java 8 (or 9), your job can be done with a regular expression:

    return strDate
            .replaceFirst("^(\\d{4}-\\d{2}-\\d{2})-(\\d{2})\\.(\\d{2})\\.(\\d{2}\\.\\d{6})$",
                    "$1T$2:$3:$4");

It's less elegant and harder to read, and it doesn't offer the level of input validation you get from using a proper date and time API. Other than that, it's a way through.

java.sql.Timestamp?

As others have said, java.sql.Timestamp offers nanosecond precision and thus will hold your date-time. Parsing your string into a Timestamp isn't straightforward, though, so I don't think it's worth the trouble. Usagi Miyanmoto correctly identifies Timestamp.valueOf() as the method to use, but before you could do that, you would have change the format, so you would end up changing the format twice instead of just once. Or maybe three times since Timestamp also doesn't produce your desired ISO 8601 format readily. Additionally you would need to decide a time zone for the timestamp, but I assume you could do that without any trouble.

If you needed to keep the the date-time around, a Timestamp object might be worth considering, but again, it's a long outdated class. In any case, for reformatting alone, I certainly would not use it.

What happened in your code?

SimpleDateFormat understood 234850 as milliseconds, that is, 234 seconds 850 milliseconds. So it added 234 seconds to your time, 11:19:02. And then printed the remaining 850 milliseconds in 6 decimal places as you had requested.

How to convert date string "yyyy-mm-ddThh:mm:ssZ" to timestamp

If converting to local time is not necessary you could just use str_to_time with correct format:

select str_to_date('2018-04-04T00:03:04Z', '%Y-%m-%dT%H:%i:%sZ')
-- 2018-04-04 00:03:04

If the timezone conversion is required then you must make sure that timezone related tables are setup correctly then use convert_tz function:

select convert_tz(str_to_date('2018-04-04T00:03:04Z', '%Y-%m-%dT%H:%i:%sZ'), 'zulu', 'system')

How to convert a "yyyy-MM-dd'T'HH:mm:ssZ'" format in a dataframe to a datetime format

This works

df = pd.DataFrame({'created_at_tweet' : ['2021-01-02T01:43:32.000Z'], 'tweet' : ['Hello Twitter!']})

df['created_at_tweet']= pd.to_datetime(df['created_at_tweet'], format=('%Y-%m-%dT%H:%M:%S.%f'))

yields

df

Java format yyyy-MM-dd'T'HH:mm:ss.SSSz to yyyy-mm-dd HH:mm:ss

SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss");
SimpleDateFormat output = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
Date d = sdf.parse(time);
String formattedTime = output.format(d);

This works. You have to use two SimpleDateFormats, one for input and one for output, but it will give you just what you are wanting.

---

Related Topics