(RoR)how to Integer convert to data by DataTime

Question

would you like tell me how to use DateTime method you may have notice,I'm not fluently English because of i'm Japanese i try writing question in English, and sorry if you are suffer from reading my English!!

i want to convert from integer to Data.i can convert from string to data, but i can convert from integer to data.

ex(using my controller):

def time_from_excel_date_time(excel_date)
   if excel_date.is_a?(String) || excel_date.is_a?(Date)
    date = excel_date.in_time_zone 
   elsif excel_date.is_a?(Integer)
     other_date = DateTime.new(excel_date).in_time_zone 
     date = other_date.in_time_zone
   else 
     return nil
   end

  date
end

=> excel_date
=> 44235
>> DateTime.new(44235).in_time_zone
=> Thu, 01 Jan 44235 09:00:00 JST +09:00

config/application

config.time_zone = 'Tokyo'
config.i18n.default_locale = :ja
config.i18n.available_locales = %i[ja en]
config.active_record.time_zone_aware_types = [:datetime]
config.active_record.belongs_to_required_by_default = true

i want to gain like a bottom shape

date = "???/???/???"

which do I?

change from (=> Thu, 01 Jan 44235 09:00:00 JST +09:00) used DateTime.new()method to ("???/???/???") by other method?

or

change from (excel_date) to ("???/???/???") by other method except method of DateTime.new()?

i try to both skill of programing and english!!

thank you for reading!!

user_15097028 · Answer

You could use Time.at(i) to create a Time, and you could use strftime to format Time.

time = Time.at(44235)     # => 1970-01-01 20:17:15 +0800

time.strftime("%Y/%m/%d") # => "1970/01/01"

user_384417 · Answer

Dates in Excel are "number of days after 31/12/1899" (the integer part) plus a fraction of day. The number 34567.89 represents 34567 days after the start of 20th century, plus around 21 hours and 22 minutes.

The simplest option is to have your "starting point" (31/12/1899 at midnight) converted to unix time (-2209078800), then add your number multiplied by 86400 (the number of seconds in a day), and finally convert to date:

EXCEL_EPOCH = -2_209_078_800
TICKS_PER_DAY = 60 * 60 * 24

def time_from_excel_date_time(excel_date)
  # when it is a number like 44235
  unix_timestamp = excel_date * TICKS_PER_DAY + EXCEL_EPOCH
  Time.at(unix_timestamp).to_datetime
end

To be clearer:

Excel gives you the number of days since 31/12/1899 (with decimals)
By multiplying by TICKS_PER_DAY you get the number of seconds since 31/12/1899.
By subtracting 2_209_078_800 you get the number of seconds since 1/1/1970 (which is the "zero" time in most programming languages).
You then pass the resulting number to Time.at to obtain an instance of Time. If you need an instance of DateTime, you can convert using .to_datetime.

I hope this is clear enough :)

(RoR)how to Integer convert to data by DataTime

2 Answers