如何在Excel的powerquery中（M函数），获取一年前的时间和当前时间unix时间戳代码？ - 飞鸟42

我想从集思录网站上直接获取数据到excel中，发现网站cookie记录了登陆时间，如果登陆时间超时，那么将不能获取到完整数据，但是cookie中记录的登陆时间是以unix时间戳的形式展现的，那么如何在powerquery中的M函数是如何把日期格式转化成时间戳格式呢？

　　上面这段代码是我翻了好久的资料（https://theexcelclub.com/working-with-unix-time-in-excel-and-power-query/），在此基础上修改的（主要是中国是东八区，所以我把初始时间点设置为了1970年1月1日8点）

但是发现集思录cookie的kbzw__Session字段每天都在变，还没找到规律，再研究下。

How to – Unix Timestamp conversions in Excel and Power Query

Unix Timestamp conversions in Excel and in Power query are becoming more and more common. With the ability to connect to a vast variety of data sources, the likelihood of you coming across a Unix timestamp is increasing. The first time I came across a UNIX-based time was when I was connecting to a database from Excel and although the field was named Time, I had no idea what all these numbers meant, and I was left totally confused.

Unix time is commonly used in operating systems and programs that timestamp transactions as they happen. The reason Unix time is used is that no matter where you live or the time zone you are in, this Unix timestamp represents a moment that is the same everywhere.

So, what is Unix time? Unix time is the duration in seconds or milliseconds from midnight of the 1st January 1970 in UCT time. It is represented by a 32-bit integer that can be positive or negative. There is however a limitation of Unix time. Unfortunately, there are upper and lower bounds which restricts the actual time span available. Unix time spans from December 13th, 1901, and will end on the 19th of January 2038. This is known as the Y38 Problem.

So now that you have a basic knowledge of Unix time, how can we work with this in Excel or Power Query? Throughout the rest of this article, you are going to learn how to carry out Unix Timestamp conversions in Excel. We will convert back and forth from Unix time to Excel time in both Excel and Power Query.

How to convert Unix time to Excel Time

Let us first explore converting Unix time to Excel Time. First, we will look at the formula when the timestamp is in second and then we will look at the formula when the timestamp is in milliseconds.

When time stamp in Seconds

The formula used to convert Unix timestamp recorded in seconds is:

= (Unix time/86400) +DATE (1970,1,1)

Remember to replace Unix Time with the cell reference that contains the Unix timestamp.

Once you enter this formula into the cell, the formula can be dragged down and copied to other cells in the workbook.

When the timestamp is in Milliseconds

The formula used to convert Unix timestamp recorded in milliseconds is:

= ((Unix time/1000)/86400) +DATE (1970,1,1)

Remember to replace Unix Time with the cell reference that contains the Unix timestamp.

How to convert Excel time to Unix time

With a reversal of the formula, we used to convert Unix time to Excel time, we can covert Excel time to Unix time.

The formula to convert Excel time to Unix time in seconds is.

= ((Excel Time – DATE(1970,1,1)) *86400

Remember to replace Excel Time in the formula with the cell reference that contains the time you wish to convert to Unix time.

The formula to convert Excel time to Unix time in milliseconds is.

= ((Excel Time -DATE (1970,1,1)) *1000) *86400

How to convert Unix time in Power query

Power query is used in both Excel and Power BI to connect to and transform data into a useable format. The more you connect to different data sources the more often you will come across date columns in Unix format. Once you have the data in Power query, a calculated column can be added to the data to carry out the conversions necessary.

How to convert from Unix time in Power Query for Excel and Power BI

From the Add Column tab on the ribbons in Power Query, select Custom Column. This will allow us to set up a new column in our data based on a calculation or formula.

When we select Custom Column, a new box will open to allow us to enter a formula. We can give our new column a name and enter the formula into the space provided. Remember to replace [Unix time] with the column in Power query that contains the Unix time.

The formula we need to enter when converting from a timestamp recorded in seconds is as follows.

=#datetime(1970,1,1,0,0,0) + duration(0,0,0,[Unix time])

The formula we need to enter when converting from a timestamp recorded in milliseconds is as follows.

=#datetime(1970,1,1,0,0,0) + duration(0,0,0,[Unix time]/1000)

Once we confirm our formula by pressing OK, a new column will appear in Power query showing the date and time.

The last step is to ensure you set this new column to the correct data type. This can be done from the Transform tab in the Power Query Ribbons.

Depending on your need, you can select between

Date/Time
Date
Time
Date/Time/Time zone
Duration

In the example shown, we have selected the data type of Date.

How to convert to Unix time in Power Query for Excel and Power BI

Using Power Query in both Excel and Power BI it is easy to convert an Excel or Power BI date and time to a Unix timestamp using a custom column as we did above.

Once we have the date-time column in Power query, Select custom column.

The function we use to convert to Unix time is:

Duration.TotalSeconds([Excel date/time column]-#datetime(1970,1,1,0,0,0) )

(remember to replace [Excel date/time column] with the column that contains the date/time that you wish to convert to Unix.)

Conclusion

The more we work with different data sources in Excel Working with Unix Time in Excel and In Power Query for Excel and Power BI will become more and more common. For this reason, we need to have the ability to convert Unix time to Excel time and Excel time to Unix time. In this article, we looked at using both formulas and Power Query to carry out Unix Timestamp conversions in Excel. If you are working with large sets of data, I would recommend the Power query option, but then if you are working with a large set of data that contains Unix timestamps, most likely you have used Power Query to connect to it in the first place.