Matlab、Excel、SQL 中的日期的数字序列形式

作者: , 共 871 字

如果一个日期(或者时间),如果用字符串,比如"2010-10-04"的形式,各个系统都没有什么区别。在某些时候,将日期用数字表示,将大大增加对日期查找、比较的效率。而如果用数字来表示日期,在不同的系统之间差别就大了。

比如 2010 年 10 月 4 日,在 Excel 中为 40455 ,在 Matlab 中为 734415 ,在 SQL 里为 40453。

事实上,当一个日期被转化为数字时,

  • Matlab 中为它到 0000-01-00 的天数,
  • SQL 中为它到 1900-01-01 的天数。
  • Excel 的日期是很奇怪的东西,它和 SQL 都是微软出品,按照官方文件的说法,它们的算法是一样的。而事实上,它们相差了两天,原因在于首先, Excel 中日期对应的数为它到 1900-01-00 的天数;其次, Excel 中日期序列多了一个不存在的日期 』1900-02-29',它是 Excel 早期设计中由于为了保持与 Lotus 1-2-3 兼容性特意这么设计的(而最开始 Lotus 这么设计则是为了编程简单)。

故对于一般的日期( 1900-03-01 以后)有以下关系式:

Matlab_datetime = Excel_datetime + 693960;
SQL_datetime = Excel_datetime - 2;
Matlab_datetime = SQL_datetime + 693962;

在 Matlab 和 Excel 之间交换日期数据时,可以直接用上面的公式,也可以考虑用下面的 mdatestr 代替 datestr ,它会自动分辨是 Matlab 系统中的日期数据还是 Excel 系统中的日期数据。

下载链接

同时,在 SQL 读取日期数据的时候需作如下处理:

% 读入日期数据到Matlab中
select CONVERT(float, mydate) + 693962
% 读入日期数据到Excel中
select CONVERT(float, mydate) + 2

Q. E. D.

类似文章:
最近用 Matlab 写了个东西,运行效率不如我意。用 Profiler 跟踪了一下,结果让我大吃一惊。其中三分之一的时间花费在 datestr 这个函数上,六分之一的时间花费在 save 和 load 数据上。这里先谈前一个问题,关于 save 和 load 的效率以后再谈。
Matlab 的 database 工具箱只支持 ODBC 数据库连接,在使用之前需要先设置 ODBC 数据源。之前在 Excel 里用的是 ADODB ,使用 ADODB 的好处是无需设置 ODBC 源,似乎效率也要高一些(但我一直没弄懂 ADODB 是什么东西,所以效率这东西我也说不清)。
编程 » SQL
现在 Access 用的人应该不多了,本来我以为我也不可能用这玩意儿,但最近在用 VBA 通过 SQL 处理数据时,发现它的语法是 Access 的语法。平时对 SQL Server 语法相对熟悉一些。下文总结了 Access 和 SQL Server 语法的差异。
2014-03-25 更新:我已经将该类修改成函数形式,并增加新功能,参见更新 Excel 的数据库查询函数库
我写了一个期权到期收益结构的 Excel 模板。欢迎大家无聊的时候使用。
这个 Excel 模板使用参数法和历史法计算资产组合的VaR,两个函数分别是 ParaVaR 和 HistVaR ,是以前写的VaR Primer的一个实现。具体使用方法可参考模板以及 VBA 的代码注释。
编程 » Excel, 数据库
在前面的文章里,我已经提到Excel 数据本身可以当做一张 SQL 查询的数据表,并在 Excel 内进行数据库运算操作。数据库查询函数可以用我之前写的Excel 数据库操作函数类。我们可以用以下方式
Excel 的数据透视表是一个很好用的功能,我写了一个 Matlab 版本,在处理上和 Excel 的透视表差不多,还差一个 filter 而已。
编程 » Excel, lotus, VBA
公司使用 Lotus ,我每天都通过 Excel 编写 VBA 代码自动调用 Lotus 发送邮件,发现一个奇怪的现象。一般我们通过下面的 VBA 代码指定邮件收件人:
Excel 多表合并和查询是一个应用很广泛的问题。下面是一个简单的例子,我们需要从两张数据表里,得出每个行业的股票波动率平均值。第一个数据表保存了股票和行业的对应关系,有两列,第一列为股票名,第二列为每只股票对应的行业。第二张表保存了各个股票在各个交易日的收盘价和前收盘价,有四列,第一列是股票名,第二列为交易日,第三列和第四列分别为股票在这个交易日的前收盘价和收盘价。
Excel、Matlab 在与数据库交互数据时,通常需要事先配置好 ODBC 数据源,这导致一个文件换到另一台机器还需要重新配置 ODBC。手工配置太麻烦,从国外一个网站看到一种解决方案:
Matlab 的 database 工具箱只支持 ODBC 数据库连接,在使用之前需要先设置 ODBC 数据源。之前在 Excel 里用的是 ADODB ,使用 ADODB 的好处是无需设置 ODBC 源,似乎效率也要高一些(但我一直没弄懂 ADODB 是什么东西,所以效率这东西我也说不清)。