Convert Durations in Ruby - Hh:Mm:Ss.Sss to Milliseconds and Vice Versa

Convert durations in Ruby - hh:mm:ss.sss to milliseconds and vice versa

How about this?

a=[1, 1000, 60000, 3600000]*2
ms="01:45:36.180".split(/[:\.]/).map{|time| time.to_i*a.pop}.inject(&:+)
# => 6336180
t = "%02d" % (ms / a[3]).to_s << ":" << 
    "%02d" % (ms % a[3] / a[2]).to_s << ":" << 
    "%02d" % (ms % a[2] / a[1]).to_s << "." << 
    "%03d" % (ms % a[1]).to_s
# => "01:45:36.180"

What is the proper way to form Rails data before I store it in my Postgres interval data-type column?

You are correct in using the interval type for saving hours, minutes and seconds. But this will not save the milliseconds.

What you did was converting a time into milliseconds. Your interval field only knows of seconds, and 6_000_000 seconds resulted in 16666:40:0 - exceeding the 1_000_000 seconds it represents, which seems to be the max limit.

You should approach this differently:

Use the interval field as intended: save hours, minutes, and seconds into a column - they are part of your form anyway. Then it will be easy to display it in the view as well.

Then, if you want to retrieve your milliseconds, simply add a method:

class Bar < ActiveRecord::Base
  def time_in_milliseconds
    # retrieve the interval seconds from the database and multiply by 1000
  end
end

Also, for handling the conversion of milliseconds to hh:mm:ss format and back you should check out this great answer.

Hive: Convert string datetime with missing seconds in yyyy-MM-dd'T'HH:mm:ss.SSS'Z'

from_unixtime is always until minutes(yyyy-MM-dd HH:mm:ss) to get millisecs we need to do some workarounds.

• we will extract the millisecs from the old_time using regexp_extract then concat that to from_unixtime result and finally cast to timestamp.

Example:

select old_time,
timestamp(concat_ws(".", --concat_ws with . and cast
FROM_UNIXTIME(UNIX_TIMESTAMP(old_time, "yyyy-MM-dd'T'HH:mm:ss.SSS'Z'"),"yyyy-MM-dd HH:mm:ss"), -- from_unixtime and unix_timestamp to convert without millisecs
regexp_extract(string(old_time),".+\\.(.*)(?i)z",1))) as newtime from --regexp_extract to extract last 3 digits before z then concat
(select string("2020-03-11T21:14:41.335Z")old_time)e

+------------------------+-----------------------+
|old_time                |newtime                |
+------------------------+-----------------------+
|2020-03-11T21:14:41.335Z|2020-03-11 21:14:41.335|
+------------------------+-----------------------+

---

UPDATE:

Your sample data have : before milliseconds, Try with below query:

select old_time,
    timestamp(concat_ws(".", --concat_ws with . and cast
    FROM_UNIXTIME(UNIX_TIMESTAMP(old_time, "yyyy-MM-dd'T'HH:mm:ss:SSS'Z'"),"yyyy-MM-dd HH:mm:ss"), -- from_unixtime and unix_timestamp to convert without millisecs
    regexp_extract(string(old_time),".+\\:(.*)(?i)z",1))) as newtime from --regexp_extract to extract last 3 digits before z then concat
    (select string("2020-03-11T21:14:41:335Z")old_time)e

Convert Sqlite Unix Epoch in milliseconds to Datetime YYYY-MM-DD HH:MM:SS.SSS

All date functions accept the same date formats and modifiers, so you can simply remove the nested datetime call.
And you need to do a floating-point division to preserve the milliseconds:

SELECT strftime('%d-%m-%Y %H:%M:%f', time/1000.0, 'unixepoch') FROM table1;

How I can convert DateTime.now in C# to yyyy-mm-dd hh:mm:ss.sssssss?

I suspect if you really need the string representation, you actually want:

string text = DateTime.UtcNow.ToString("yyyy-MM-dd HH:mm:ss.fffffff", 
                                       CultureInfo.InvariantCulture)

Note that:

• Unless you really want the local time, use UtcNow instead of Now. For timestamps, you almost always want UTC.
• I doubt that you want to use the time separator of the current culture, hence the specification of CultureInfo.InvariantCulture
• "MM" means months whereas "mm" means minutes
• "HH" means 24-hour clock whereas "hh" means 12-hour clock
• "ff..." is used for fractions of a second

See MSDN for more details of custom date and time formatting.

However, I'd strongly recommend that you try to avoid string conversions wherever possible. Why can't your stored procedure just use the relevant DateTime type instead of a string representation? Then you can pass the value to the stored procedure as a DateTime (via a command parameter) and you can get rid of the error-prone string conversion clutter.

DateTime Conversion to Unix Epoch Adding Phantom Hour

A few things:

• If possible, you should work with DateTimeOffset instead of DateTime for this type of operation. DateTimeOffset is always a specific moment in time, while DateTime might be, or might not be, depending on Kind and how well you adhere to the subtleties of how Kind is interpreted by various methods.

• If you do use DateTimeOffset, and you are targeting .NET 4.6 or greater (or .NET Core), then you can use the built in DateTimeOffset.FromUnixTimeMilliseconds and ToUnixTimeMilliseconds methods, rather than creating your own.

• You might consider using the Noda Time open source library, as it adds significant value to most applications working with date and time.

  • For instance, if you want to work with tzdb time zones like the "Europe/London" zone you mentioned, then you can use DateTimeZoneProviders.Tzdb["Europe/London"].

Now the rest of my answer assumes you don't take any of the above recommendations, and pertains to the code you provided in the question.

• You have UnixEpoch implemented as a static method. Since its value never changes, it should probably be implemented as a static property, with a private readonly backing field. It could also be implemented as a public static readonly field, though most people prefer exposing those via properties. (These are just coding guidelines, but don't introduce any error.)

• In your FromMillisecondsSinceUnixEpoch method, you are calling .ToLocalTime(). That should be omitted. You don't need to call .ToUniversalTime() either. Just return the result of adding the milliseconds. The Kind will be Utc. If you need to work with local time, do the conversion later - not inside this function.

• Recognize that the ID "GMT Standard Time" is for London, not UTC. London is either GMT (UTC+00:00) or BST (UTC+01:00) depending on the date and time in question.

• Recognize that DateTime.ToLocalTime and DateTime.ToUniversalTime convert between UTC and the current local time zone on the machine where the code is running. That might be London, or might be something else depending on your use case. If you're running on a server, such as in an ASP.Net web application, then it's not a good practice to rely on the system local time zone.

• In the code you showed with TimeZoneInfo.ConvertTime, since you didn't assign DateTimeKind.Utc to the input, that value will have DateTimeKind.Unspecified. ConvertTime will interpret that as already belonging to the source time zone. Since you've given the same destination time zone, then in most cases this will be a no-op.

• In that same function, it's invalid to define unixEpoch in terms of London time, for the reasons specified earlier. Also note that on 1970-01-01, London was not on GMT, but actually on BST (known as "British Standard Time" back then, rather than "British Summer time"). TZDB knows about this, but it's too far back for Windows time zones and TimeZoneInfo. The "GMT Standard Time" Windows zone only reflects the current rules around BST/GMT, not those that were in effect at the time.

As far as converting the Java code you supplied, that function reads a string in ISO 8601 format with millisecond precision, interprets it in the London time zone, converts it to UTC, and gives the time in milliseconds since the Unix epoch. There are a few ways you could do that:

• .Net 3.5+

  public static long UkTimeStringToUtcMillis(string s)
  {
      string format = "yyyy-MM-dd'T'HH:mm:ss.FFF";
      DateTime dt = DateTime.ParseExact(s, format, CultureInfo.InvariantCulture);
      TimeZoneInfo tz = TimeZoneInfo.FindSystemTimeZoneById("GMT Standard Time");
      DateTime utc = TimeZoneInfo.ConvertTimeToUtc(dt, tz);
      DateTime epoch = new DateTime(1970, 1, 1, 0, 0, 0, DateTimeKind.Utc);
      return (long) (utc - epoch).TotalMilliseconds;
  }
  

• .Net 4.6+ / .Net CoreCLR

  public static long UkTimeStringToUtcMillis(string s)
  {
      string format = "yyyy-MM-dd'T'HH:mm:ss.FFF";
      DateTime dt = DateTime.ParseExact(s, format, CultureInfo.InvariantCulture);
      TimeZoneInfo tz = TimeZoneInfo.FindSystemTimeZoneById("GMT Standard Time");
      TimeSpan offset = tz.GetUtcOffset(dt);
      DateTimeOffset dto = new DateTimeOffset(dt, offset);
      return dto.ToUnixTimeMilliseconds();
  }
  

• Noda Time

  public static long UkTimeStringToUtcMillis(string s)
  {
      LocalDateTimePattern pattern = LocalDateTimePattern.ExtendedIsoPattern;
      LocalDateTime dt = pattern.Parse(s).Value;
      DateTimeZone tz = DateTimeZoneProviders.Tzdb["Europe/London"];
      Instant i = dt.InZoneLeniently(tz).ToInstant();
      return i.Ticks / NodaConstants.TicksPerMillisecond;
  }
  

Change yyyy-mm-ddThh-mm-ss to datetime, throw away T separator and milliseconds

For example:

import time
print time.strftime("%Y-%m-%d %H:%M:%S")
# 2018-06-28 08:00:35

As to your question:

import datetime
str_time = '2018-03-08T08:00:00.000'
d = datetime.datetime.strptime(str_time, "%Y-%m-%dT%H:%M:%S.%f")
print d
# 2018-03-08 08:00:00

New method:

import time

str_time = '2018-03-08T08:00:00.000'
d = time.strptime(str_time, "%Y-%m-%dT%H:%M:%S.%f")

print d
# time.struct_time(tm_year=2018, tm_mon=3, tm_mday=8, tm_hour=8, tm_min=0, tm_sec=0, tm_wday=3, tm_yday=67, tm_isdst=-1)

print time.strftime("%Y-%m-%d %H:%M:%S", d)
# 2018-03-08 08:00:00

---

Related Topics