Storing date and time as epoch vs native datetime format in the database

Posted by zakovyrya on Stack Overflow See other posts from Stack Overflow or by zakovyrya
Published on 2010-04-12T17:23:19Z Indexed on 2010/04/12 17:43 UTC
Read the original article Hit count: 338

Filed under:

datetime

|

epoch

|

scripting-language

For most of my tasks I find it much easier to work with date and time in the epoch format:

it's trivial to calculate timespan

or determine if some event happened before or after another,

I don't have to deal with time-zone issues if the data comes from different geographical sources,

in case of scripting languages what I usually get from database when I request a datetime-typed column is a string that I need to parse in order to work with it.

This list can go on, but for me in order to keep my code portable that's enough to ditch database's native datetime format and store date and time as integer. What do you guys think?

© Stack Overflow or respective owner

Related posts about datetime

Finding begin and end of year/month/day/hour

as seen on Stack Overflow - Search for 'Stack Overflow'
I'm using the following snipped to find the begin and end of several time periods in Joda. The little devil on my left shoulder says thats the way to go... but I dont believe him. Could anybody with some joda experience take a brief look and tell me that the little guy is right? (It will be only… >>> More
Get non-overlapping dates ranges for prices history data

as seen on Stack Overflow - Search for 'Stack Overflow'
Hello, Let's assume that I have the following table: CREATE TABLE [dbo].[PricesHist]( [Product] varchar NOT NULL, [Price] [float] NOT NULL, [StartDate] [datetime] NOT NULL, [EndDate] [datetime] NOT NULL ) INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples'… >>> More
DateTime.MinValue vs new DateTime() in C#

as seen on Stack Overflow - Search for 'Stack Overflow'
When getting SQL DateTime Resharper suggests to use new DateTime() when value is DBNull.Value. I've always used DateTime.MinValue. Which is the proper way? DateTime varData = sqlQueryResult["Data"] is DateTime ? (DateTime) sqlQueryResult["Data"] : new DateTime(); >>> More
SOLVED: Error 1 Ticks must be between DateTime.MinValue.Ticks and DateTime.MaxValue.Ticks

as seen on Dot net Slackers - Search for 'Dot net Slackers'
This is a simple looking error message that is deceptively hard to track down. Thankfully if you're having this problem then this article should get you back on track without spending hours scratching your head. Scenario It was time to update an existing website so after synchronising my copy of… >>> More
Converting Openfire IM datetime values in SQL Server to / from VARCHAR(15) and DATETIME data types

as seen on Geeks with Blogs - Search for 'Geeks with Blogs'
A client is using Openfire IM for their users, and would like some custom queries to audit user conversations (which are stored by Openfire in tables in the SQL Server database). Because Openfire supports multiple database servers and multiple platforms, the designers chose to store all date/time… >>> More

Related posts about epoch

Epoch is not epoch if do a new Date(0L). Why ?

as seen on Stack Overflow - Search for 'Stack Overflow'
My problem is pretty straigtforward explained : if i do this : public class Main { public static void main(String[] args) throws Exception { Date d = new Date(0L ); System.out.println(d); } } I get the following output : Thu Jan 01 01:00:00 CET 1970 According to the doc, i… >>> More
Python 3: timestamp to datetime: where does this additional hour come from?

as seen on Stack Overflow - Search for 'Stack Overflow'
I'm using the following functions: # The epoch used in the datetime API. EPOCH = datetime.datetime.fromtimestamp(0) def timedelta_to_seconds(delta): seconds = (delta.microseconds * 1e6) + delta.seconds + (delta.days * 86400) seconds = abs(seconds) return seconds def datetime_to_timestamp(date… >>> More
Convert epoch time to iso8601 format using jQuery

as seen on Stack Overflow - Search for 'Stack Overflow'
I want to convert an epoch time (seconds since midnight 1/1/1970) to iso8601 format combined date and time in UTC ("2010-03-14T09:12Z" or "Tue Mar 14 21:02:31 +0000 2010") using jQuery code in a plugin. Any help greatly appreciated. Thanks! >>> More
Get seconds since epoch in any POSIX compliant shell

as seen on Stack Overflow - Search for 'Stack Overflow'
I'd like to know if there's a way to get the number of seconds since the UNIX epoch in any POSIX compliant shell, without resorting to non-POSIX languages like perl, or using non-POSIX extensions like GNU awk's strftime function. Here are some solutions I've already ruled out... date +%s //… >>> More
unix date, number of seconds since epoch

as seen on Stack Overflow - Search for 'Stack Overflow'
Is there a way to use unix date to print the number of seconds since epoch? I'm open to using other standard shell commands if there is a way (I'm using Solaris, so there isn't date "+"%s") Thanks >>> More