在MS Access SQL查询中从正常日期转换为Unix纪元日期 [英] Converting from normal date to unix epoch date in MS Access SQL query

问题描述

我正在尝试为通过ODBC与MySQL数据库的MS Access 2007连接编写查询.一切工作正常，查询执行了我想要做的事情.我挂断的部分是，我一直在问用户Unix纪元时间，而不是常规日期.

I'm trying to write a query for an MS Access 2007 connection to a MySQL database through ODBC. Everything's working fine, and the query does what I want it to do. The part that I'm hung up on is that I'm stuck asking the user for unix epoch time, instead of a regular date.

我查找了一堆有关MS Access的引用，尽管有许多我可以在SQL调用中使用的日期转换函数，但是找不到可以用于从正常日期转换的任何日期->Unix纪元日期.

I looked up a bunch of references for MS Access, and while there are a number of date conversion functions I can use in the SQL call, I can't find any that I can use to convert from a normal date -> unix epoch date.

我想，并且我认为这可行，是以一种更加友好的方式(人类可读的日期)向用户询问日期，然后将其转换为unich纪元日期.现在，我考虑了一下，我想我的另一种选择是在使用SQL查询将其绘制出来之后，在数据库中转换unix纪元日期，但我宁愿尽可能地转换用户的输入，因为更少的了.输入，这样我就不必做太多的工作.

What I would like, and I assume this works, is to ask the user for the date in a much kinder fashion (a human readable date), and then convert it into unix epoch date. Now that I think about it, I guess my other option is to convert the unix epoch dates in the database after drawing them out with the SQL query, but I'd rather convert the user's input if at all possible as there is less of that input so I wouldn't have to do as much work.

SELECT TOP 5 Count( * ) AS [Number of visits by language], login.lang AS [Language]
FROM login, reservations, reservation_users
WHERE (reservations.start_date Between [Starting unix epoch time] And [Ending unix epoch time]) And reservations.is_blackout=0 And reservation_users.memberid=login.memberid And reservation_users.resid=reservations.resid And reservation_users.invited=0
GROUP BY login.lang
ORDER BY Count( * ) DESC;

推荐答案

不知道这是否行得通(没有访问权限进行确认)，但是请尝试从这里提出建议:

Don't know if this is going to work (don't have Access to confirm), but try the suggestion from here:

• http://weblogs.sqlteam.com/jeffs/archive/2007/04/13/format-date-sql-server.aspx

将要使用的:

    DATEDIFF(second, '1 Jan 1970', tbl.LastChangeDate)

这是从一些评论中摘录的.

This is from some of the comments.

请参阅Remou的评论.

See the comment from Remou.

这篇关于在MS Access SQL查询中从正常日期转换为Unix纪元日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！