Join 34,000+ subscribers and receive articles from our blog about software quality, testing, QA and security.
 

Format of Dates in Database


#1

I need to write a query that returns the create date and update date. These dates are stored as integers and I cannot tell what the format is by looking at it. Can someone please tell me how I can convert these integers into a mm/dd/year hh:mm format?


#2

They are Epoch format - used extensively by Unix. It is the # of seconds since 1/1/1970. Here is a sample:

SELECT
g.name as Group_Name, c.id as CaseID, s.name as SuiteId, s.name as SectionName,
c.title as Case_Title, su.name as Suite_Name, u.name as UserName,
p.name as ProjectName, c.updated_by, c.user_id, u.name, u1.name,
DATEADD(ss, c.created_on , ‘1/1/1970’) AS CreateDate,
DATEADD(ss, c.updated_on , ‘1/1/1970’) AS UpdateDate,
c.created_on, c.updated_on,
c.custom_stepsincl

This is how to get the current date in Epoch format:
EpochDate = DateDiff(“S”, “1/1/1970”, Now())

I use the above in a VBA userform but I believe it should work on SQL as well.


#3

This is exactly what I needed. Thanks!


#4

No problem - glad to help.


#5

What time zone is used for storing dates in SQL DB?


#6

Hello Alex,

Timestamps are stored in Unix timestamp format which are the seconds since 1970 midnight UTC:

Almost all programming languages have support for this format and it’s usually easy to convert to the date/time structure.

I hope this helps!

Cheers,
Tobias